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" <(E-Mail Removed)> wrote in message
news:E2FBF42A-B5F5-40C4-AEC1-(E-Mail Removed)...
>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?
>
|
Similar Threads
