Allowing users to run macros on protected worksheets

[SDUNFORD]

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?

 

[Alan]

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]

Thanks!!

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."

 

[Alan]

Your welcome.

Thanks!!