worksheet protection

  • Thread starter Thread starter david
  • Start date Start date
D

david

I have a workbook that is going to be given to multiple
people. I want all of them to be able to look at all of
the pages, but then only be able to change information on
their page. I have pull down lists on the worksheets that
won't lock. How can I get all of these things to work? I
have no problem with assigning separate passwords per
sheet. I just need to know how to get it to work right.

Thanks,
David
 
I assume your pulldown lists are done with Data=>Validation.

It sounds like you are willing to allow each person to unprotect their own
sheet. If so, you could make the list dependent on a value in a locked cell
in each sheet, so that if the cell is blank, as an example, the list offers
no choices and the cell is protected. If the person unlocks the sheet and
enters a value in the locked cell, then the list appears as normal.

You would need to used defined names to build the lists.

I haven't tried it, but it seems like it could be done. You would be
dependent on the authorized user of the sheet clearing that field before
reprotecting the sheet.

An alternative would probably be a macro based approach.

Regards,
Tom Ogilvy
 
David,

There are any number of ways to do this:

In the ThisWorkbook module you can have a Worksheet_Activate event
that will call
myuser = Application.UserName
If Ucase(myuser) = Ucase("david") then
Activesheet.Unprotect "password"
End If

You will have to build in a list of user names and sheet names to make it
user
and sheet specific.

Or you could call an input box to supply a password
pswrd =InputBox("Enter Password")
If pswrd ="Xyz" and Activesheet.Name="usernameXyz"
Watch for uppercase and lower case. (check my first example to avoid that
trap).

You can also build a Select Case to include all of your users and their
page.

Select Case Activesheet.Name
Case Sheet1
pswrd = "abc"
Case Sheet2
pswrd = "efg"
Case Sheet3 ..............

Remember to protect your VBA to prevent people seeing the passwords and
messing with your code (not perfect since Excel is not that secure).

Hope this gives you a place to start...

steve
 
of course the user could disable macros and this would all be for naught. -
just a thought.
 
Tom and David:

I'm just getting my feet wet here, so take my comments with a huge grain of
salt. But I wonder if the following would work.

I have a shared workbook on a company-wide server. I have one sheet that
has a warning that macros must be enabled to view the data. Using a
Workbook_Open event, every other worksheet except that one is VeryHidden on
open, and - if macros are enabled - a message box pops up asking them if
they would like to view the data. No macros, no message box, no data.
Click the button, and the first sheet is VeryHidden and the data is
available.

Now, if every sheet were protected, a second message box could ask for the
user's name. Could that user name then be matched to a validation list that
would run code to unprotect only that user's sheet?

Or, if that might leave things too vulnerable to someone entering another
user's name, isn't there a way to get the system user's name? If so, could
that be used to unprotect a certain sheet? That would prevent other users
from entering the wrong name to get to data not theirs to change.

Ed
 
Back
Top