Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing users to run macros on protected worksheets
I am trying to allow users of my workbook to run macros on protected
worksheets. I have tried using the following code: Private Sub Workbook_Open() Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet End Sub When I then open the workbook, it says that the password I provided is incorrect. I changed the "Secret" password to the correct one and that didn't work. I also tried using "Secret" as the password to protect the worksheets and that still didn't work. Does anyone have any suggestions or a different way to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing users to run macros on protected worksheets
If you are trying to unprotect the worksheets on Open, why are they
protected? Including a module in every Workbook increases the file size of the file, increasing costs for the company, in storage. This is compounded when you have multiple users saving off individual copies of the same file. I would suggest that the user macros include the code to UnProtect the worksheets. Include this code at the beginning of each macros your users are running: Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.UnProtect Password:="Secret" Next wSheet Include this at the end of the user macros: For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet This way, the sheets are only UnProtected when user macro runs, and protected when user macro completes. If you want the file contain the module, then change "Protect" to "UnProtect" and remove "UserInterFaceOnly:=True". The worksheets will remain unprotected unless you put more code in it to reprotect the sheets on a workbook close event. Again, you are increasing the file size even more. Alan "The only dumb question is a question left unasked." "SDUNFORD" wrote in message ... I am trying to allow users of my workbook to run macros on protected worksheets. I have tried using the following code: Private Sub Workbook_Open() Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet End Sub When I then open the workbook, it says that the password I provided is incorrect. I changed the "Secret" password to the correct one and that didn't work. I also tried using "Secret" as the password to protect the worksheets and that still didn't work. Does anyone have any suggestions or a different way to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing users to run macros on protected worksheets
Thanks!!
"Alan" wrote: If you are trying to unprotect the worksheets on Open, why are they protected? Including a module in every Workbook increases the file size of the file, increasing costs for the company, in storage. This is compounded when you have multiple users saving off individual copies of the same file. I would suggest that the user macros include the code to UnProtect the worksheets. Include this code at the beginning of each macros your users are running: Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.UnProtect Password:="Secret" Next wSheet Include this at the end of the user macros: For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet This way, the sheets are only UnProtected when user macro runs, and protected when user macro completes. If you want the file contain the module, then change "Protect" to "UnProtect" and remove "UserInterFaceOnly:=True". The worksheets will remain unprotected unless you put more code in it to reprotect the sheets on a workbook close event. Again, you are increasing the file size even more. Alan "The only dumb question is a question left unasked." "SDUNFORD" wrote in message ... I am trying to allow users of my workbook to run macros on protected worksheets. I have tried using the following code: Private Sub Workbook_Open() Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet End Sub When I then open the workbook, it says that the password I provided is incorrect. I changed the "Secret" password to the correct one and that didn't work. I also tried using "Secret" as the password to protect the worksheets and that still didn't work. Does anyone have any suggestions or a different way to do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing users to run macros on protected worksheets
Your welcome.
"SDUNFORD" wrote in message ... Thanks!! "Alan" wrote: If you are trying to unprotect the worksheets on Open, why are they protected? Including a module in every Workbook increases the file size of the file, increasing costs for the company, in storage. This is compounded when you have multiple users saving off individual copies of the same file. I would suggest that the user macros include the code to UnProtect the worksheets. Include this code at the beginning of each macros your users are running: Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.UnProtect Password:="Secret" Next wSheet Include this at the end of the user macros: For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet This way, the sheets are only UnProtected when user macro runs, and protected when user macro completes. If you want the file contain the module, then change "Protect" to "UnProtect" and remove "UserInterFaceOnly:=True". The worksheets will remain unprotected unless you put more code in it to reprotect the sheets on a workbook close event. Again, you are increasing the file size even more. Alan "The only dumb question is a question left unasked." "SDUNFORD" wrote in message ... I am trying to allow users of my workbook to run macros on protected worksheets. I have tried using the following code: Private Sub Workbook_Open() Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet End Sub When I then open the workbook, it says that the password I provided is incorrect. I changed the "Secret" password to the correct one and that didn't work. I also tried using "Secret" as the password to protect the worksheets and that still didn't work. Does anyone have any suggestions or a different way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allowing different users access to different worksheets | Excel Discussion (Misc queries) | |||
Allowing users to use filters and sort on protected pivot table | Excel Discussion (Misc queries) | |||
Allowing users to use filters and sort on protected pivot table | Excel Discussion (Misc queries) | |||
How can I lock worksheets while still allowing macros to operate? | Excel Discussion (Misc queries) | |||
Allowing spell check on protected worksheets | Setting up and Configuration of Excel |