Storing and Accessing Data

[Matt]

I'm in the process of constructing a workbook where only certain people will
have access to certain worksheets within the workbook. Depending on the
user, I want some worksheets to be unviewable.

The best way I've found to implement this idea is to hide the various
worksheets (Format -> Sheet -> Hide) and then password protect the workbook.
Then, I've created macro functions that prompts the user for a password.
(This is not the same password used to protect the workbook.) When the
password is entered, the function unprotects the workbook, unhides the target
worksheet, and protects the workbook again - so that the target worksheet can
be viewed.

This seems to work simply enough. My problem is that I'm not sure how to
store the passwords. This workbook will need to be accessed by as many as 20
different people, each of whom will have access to their own worksheet
requiring it's own unique password. Because of the multiple passwords, I
need a way to store all of the password information and pull the data when a
user enters a password (Without the password being visible anywhere in the
workbook).

Any help or suggestions would be greatly appreciated. Thank you.

 

[Robert Crandal]

I wrote a thread a few days ago that was titled
"one workbook, multiple users". It's not exactly the
same situation as yours, but it involved multiple people
reading and writing to the same workbook. Here
are two responses that I received:

* "Just don't do it. Excel file sharing for a purpose like this just
creates
more headaches than it's worth."
If it's important to use excel instead of a database to collect data from
users, then create a template file, give them each a copy, and have them
store the copies in a common location with a consistent naming
convention.
Then use VBA to automate the collection of the data from their separate
files."

* "Just because somebody tells you to jump off a cliff doesn't mean that
you should do it! You asked for recommendations and we gave you our
recommendations."

So.....the whole idea of multiple users accessing the same workbook might
not be a feasible idea. I'm determined to find a workaround to this
limitation,
but for now I'm assuming that multi-user workbooks are too much trouble
than they are worth.

8(