Allowing users to run macros on protected worksheets

S

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?
 
A

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

SDUNFORD

Thanks!!

Alan said:
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."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top