VBA Code for Worksheet Password is protecting other open workbooks - how do I change the code?

[Joyner]

Hello and thank you for any help.

I have a very complicated Macro automated Excel Workbook that has numerous worksheets with a lot of vba routines. A lot of the routines protect and unprotect the worksheets with a password. There are now reports of other open workbooks being password protected and I have traced it to this particular workbook code.

I also believe it is related to code on the worksheet modules where there is code for Activate, and possibly other Worksheet events, but I'm not sure.


In one of my regular Modules there is:

Public Const sPW = "abc"

And usually the password code is simple:

ActiveSheet.Unprotect sPW

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, Password:=sPW



Sometimes the codes specifically refers to the sheet:

Sheets("xyz").Unprotect sPW

Sheets("xyz").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True, Password:=sPW



But I "think" anything on the Worksheet events is simply

ActiveSheet.Protect Password:=sPW

And I think that is where my problem is.

So my question is, can I make one change somewhere to make sure my code is not password protecting other Workbooks, like change this:

Public Const sPW = "abc"

or will I need to change the code everywhere to ensure it specifically refers to the Worksheet in question like:

Sheets("xyz").Protect

Thank you

 

[Fluff]

The safest option would be something like

ThisWorkbook.Sheets("xyz").Protect

That way it will only affect sheets in the workbook containing the code.

 

[frank_AL]

Yes, what Fluff said!

If your routine is working with multiple Workbooks or you/the user might have other Workbooks open when the VBA module is executed the fact your code chooses "ActiveWorkbook" could be the issue. My recommendation is that you declare variables for your Workbooks and then reference them by the variable name assigned rather that just using "ActiveWorkbook".

For example:
Dim wb as Workbook

set wb = Workbooks("Example")
With wb
ActiveSheet.Protect Password:=sPW
End With

 

[Joyner]

The safest option would be something like

ThisWorkbook.Sheets("xyz").Protect

That way it will only affect sheets in the workbook containing the code.

Okay, thank you, I thought that may be the case.

One question since I am not the best at VBA and I always seem to run into issues for some syntax that seems straight forward.

should this:

ThisWorkbook.Sheets("xyz").Protect Password:=sPW


substitute for this without any issues?

ActiveSheet.Protect Password:=sPW

 

[Fluff]

As long as the sheet you want to protect/unprotect is in the same workbook as the code, then yes.