I have a macro enabled workbook with protected worksheets that require the sheet protection be removed each time the macros are run and then added back on completion. I am looking to create a function to do this efficiently whenever any worksheet selected, and instead of putting the code at the beginning and end of each macro.
Also, How would you hide or obfuscate the vba protection password (Cypher) instead of displaying it somewhere in the code? I also find that I have to add GetInfo() to each module, instead of in one place. If you make it public, then it is available to a User in the macro pulldown of the workbook.
Here is a typical example of the code I am using when I only need to run a macro on a single protected sheet.
Alternatively, I use the following code when working on all Worksheets:
Also, How would you hide or obfuscate the vba protection password (Cypher) instead of displaying it somewhere in the code? I also find that I have to add GetInfo() to each module, instead of in one place. If you make it public, then it is available to a User in the macro pulldown of the workbook.
Here is a typical example of the code I am using when I only need to run a macro on a single protected sheet.
Code:
Sub (UnprotectSheet)
‘ Get the Cypher to unprotect the worksheets
GetInfo
‘Unprotect active worksheet
ActiveSheet.Unprotect Password:=(Cypher)
‘**********************
‘ Run some VBA on active worksheet
‘**********************
‘Protect active worksheet
ActiveSheet.Protect Password:=(Cypher)
End Sub
Alternatively, I use the following code when working on all Worksheets:
Code:
Sub (UnprotectAll)
‘ Get the Cypher to unprotect the worksheets
GetInfo
‘Unprotect all sheets in workbook
For Each Sh In ActiveWorkbook.Worksheets
Sh.Unprotect Password:=(Cypher)
Next Sh
‘**********************
‘ Run some VBA on multiple sheets
‘**********************
‘Protect all sheets in workbook
GetInfo
For Each Sh In ActiveWorkbook.Worksheets
Sh.Protect Password:=(Cypher)
Next Sh
End Sub
Private Sub GetInfo()
Cypher = "abc123"
End Sub
Last edited: