Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet protection
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet protection
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet protection
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 "david" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet protection
of course the user could disable macros and this would all be for naught. -
just a thought. -- Regards, Tom Ogilvy steve wrote in message ... 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 "david" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet protection
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 "Tom Ogilvy" wrote in message ... of course the user could disable macros and this would all be for naught. - just a thought. -- Regards, Tom Ogilvy steve wrote in message ... 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 "david" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet protection | Excel Discussion (Misc queries) | |||
Worksheet protection | Excel Discussion (Misc queries) | |||
Worksheet Protection | Excel Discussion (Misc queries) | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
Worksheet protection is gone and only wokbook protection can be se | Excel Discussion (Misc queries) |