| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Protection
I have a Workbook in which I want the Worksheets to be protected. Everything
is done with MACROS, and the MACROS both Unprotect and Protect the Worksheets. I manually Protect the Worksheet and save it. Then I retrieve it and run one of the MACROS. It asks for the PSW going in, but not coming out. Then I save it and go thru the process again. This time too, the Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor does it ask for the PSW if I Unprotect it manually. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet Protection
Sounds like you are omitting the password argument when the macros
protect/unprotect the worksheet. If you manually set a password then use code to unprotect the sheet without specifying the password (in your code) Sheet1.Unprotect you will still get the dialog box to input a password. When the sheet is protected, you'll need to include the password with your code Sheet1.Protect password:="your_password" "TomD" wrote: I have a Workbook in which I want the Worksheets to be protected. Everything is done with MACROS, and the MACROS both Unprotect and Protect the Worksheets. I manually Protect the Worksheet and save it. Then I retrieve it and run one of the MACROS. It asks for the PSW going in, but not coming out. Then I save it and go thru the process again. This time too, the Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor does it ask for the PSW if I Unprotect it manually. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet Protection
Hi JMB,
Thanks for the reply. Gosh, that's not too good to have the PSW hard coded. That means that anybody can run the MACROS and make changes to the data. Isn't there any way via MACROS that the PSW can be a manual function? TomD "JMB" wrote: Sounds like you are omitting the password argument when the macros protect/unprotect the worksheet. If you manually set a password then use code to unprotect the sheet without specifying the password (in your code) Sheet1.Unprotect you will still get the dialog box to input a password. When the sheet is protected, you'll need to include the password with your code Sheet1.Protect password:="your_password" "TomD" wrote: I have a Workbook in which I want the Worksheets to be protected. Everything is done with MACROS, and the MACROS both Unprotect and Protect the Worksheets. I manually Protect the Worksheet and save it. Then I retrieve it and run one of the MACROS. It asks for the PSW going in, but not coming out. Then I save it and go thru the process again. This time too, the Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor does it ask for the PSW if I Unprotect it manually. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet Protection
You can get the password from the user via an Inputbox or a userform. I was
thinking along the lines of: Sub Test() Dim PSW As String On Error GoTo CleanUp PSW = InputBox("Enter Password") Sheet1.Unprotect password:=PSW 'Do some stuff Sheet1.Protect password:=PSW CleanUp: If Err.Number < 0 Then _ MsgBox "Error " & Err.Number & _ ": " & Err.Description End Sub A userform would allow you to use a textbox, which has a passwordchar property that allows you to display a particular character (such as "*") instead of displaying what the user is typing in the textbox. If the unprotect and protect statements are not in the same sub, you could set PSW up as a global variable (see VBA help to learn more about a variables "Scope"). A word of caution if you have sensitive data - worksheet protection can easily be broken w/ a macro. Most average users may not figure it out (or care to even try), but just don't be fooled into thinking your data is safe from prying eyes. Read more he http://www.mcgimpsey.com/excel/removepwords.html "TomD" wrote: Hi JMB, Thanks for the reply. Gosh, that's not too good to have the PSW hard coded. That means that anybody can run the MACROS and make changes to the data. Isn't there any way via MACROS that the PSW can be a manual function? TomD "JMB" wrote: Sounds like you are omitting the password argument when the macros protect/unprotect the worksheet. If you manually set a password then use code to unprotect the sheet without specifying the password (in your code) Sheet1.Unprotect you will still get the dialog box to input a password. When the sheet is protected, you'll need to include the password with your code Sheet1.Protect password:="your_password" "TomD" wrote: I have a Workbook in which I want the Worksheets to be protected. Everything is done with MACROS, and the MACROS both Unprotect and Protect the Worksheets. I manually Protect the Worksheet and save it. Then I retrieve it and run one of the MACROS. It asks for the PSW going in, but not coming out. Then I save it and go thru the process again. This time too, the Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor does it ask for the PSW if I Unprotect it manually. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Worksheet Protection
Thanks for the info. I will give it a try and let you know. Looks good!
"JMB" wrote: You can get the password from the user via an Inputbox or a userform. I was thinking along the lines of: Sub Test() Dim PSW As String On Error GoTo CleanUp PSW = InputBox("Enter Password") Sheet1.Unprotect password:=PSW 'Do some stuff Sheet1.Protect password:=PSW CleanUp: If Err.Number < 0 Then _ MsgBox "Error " & Err.Number & _ ": " & Err.Description End Sub A userform would allow you to use a textbox, which has a passwordchar property that allows you to display a particular character (such as "*") instead of displaying what the user is typing in the textbox. If the unprotect and protect statements are not in the same sub, you could set PSW up as a global variable (see VBA help to learn more about a variables "Scope"). A word of caution if you have sensitive data - worksheet protection can easily be broken w/ a macro. Most average users may not figure it out (or care to even try), but just don't be fooled into thinking your data is safe from prying eyes. Read more he http://www.mcgimpsey.com/excel/removepwords.html "TomD" wrote: Hi JMB, Thanks for the reply. Gosh, that's not too good to have the PSW hard coded. That means that anybody can run the MACROS and make changes to the data. Isn't there any way via MACROS that the PSW can be a manual function? TomD "JMB" wrote: Sounds like you are omitting the password argument when the macros protect/unprotect the worksheet. If you manually set a password then use code to unprotect the sheet without specifying the password (in your code) Sheet1.Unprotect you will still get the dialog box to input a password. When the sheet is protected, you'll need to include the password with your code Sheet1.Protect password:="your_password" "TomD" wrote: I have a Workbook in which I want the Worksheets to be protected. Everything is done with MACROS, and the MACROS both Unprotect and Protect the Worksheets. I manually Protect the Worksheet and save it. Then I retrieve it and run one of the MACROS. It asks for the PSW going in, but not coming out. Then I save it and go thru the process again. This time too, the Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor does it ask for the PSW if I Unprotect it manually. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet protection status | Excel Discussion (Misc queries) | |||
| Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
| Worksheet tab protection | Excel Discussion (Misc queries) | |||
| copyright and worksheet protection | Excel Discussion (Misc queries) | |||
| Seeking help for total worksheet protection | Excel Discussion (Misc queries) | |||
Linear Mode
