Sheets Protection Macro Not Working Properly

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
141
Office Version
  1. 2013
Platform
  1. Windows
I have a Worksheets Protection macro which used to work perfectly. It used to protect all worksheets in the Workbook. I think when I added a very hidden sheet this stopped it looping through all the sheets and protecting but that I could deal with. It was protecting the sheet that was active which was fine for my needs. Now that I have added Edit objects and format cells to the code it is not working properly.

If I run the macro from the module or from the list of macros it works fine on the active sheet. I am still able to format cells whilst formula remain protected. I have added a keyboard shortcut (Ctrl + Shift P) to fire the macro. If I run it using the keyboard shortcut it is protecting the sheet and not allowing formatting of cells. I don't under stand why. Any ideas? Here is the code:

VBA Code:
Sub ProtectSelectedWorksheets() 'Shortcut is Ctr + Shift P

Dim ws As Worksheet
Dim sheetArray As Variant
Dim myPassword As Variant

'Set the password - Please use 63360
myPassword = Application.InputBox(Prompt:="Enter password", _
    Title:="Password", Type:=2)

'If Cancel is clicked
If myPassword = False Then Exit Sub

'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each worksheet in the active workbook
For Each ws In sheetArray

    On Error Resume Next
                   
     ws.Select   'Select the worksheet
        
    ws.Protect password:=myPassword, DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True 'Protect each worksheet but enables edit objects & formatting _
        cells (for Comment generating macros etc)
  
    On Error GoTo 0
        
Next ws

sheetArray.Select

End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You say you want to protect all sheets but your code is only protecting the Selected sheets.
Since you can't select the hidden sheets it will never change the protection on them.

Current code
VBA Code:
'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each worksheet in the active workbook
For Each ws In sheetArray

If you want to protect all the sheets why not get rid of the Set sheetArray and just loop through all the sheets.
VBA Code:
'Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
 
Upvote 0
You say you want to protect all sheets but your code is only protecting the Selected sheets.
Since you can't select the hidden sheets it will never change the protection on them.

Current code
VBA Code:
'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each worksheet in the active workbook
For Each ws In sheetArray

If you want to protect all the sheets why not get rid of the Set sheetArray and just loop through all the sheets.
VBA Code:
'Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
Thanks. That is working better.
The other issue I think is my laptop. On my work pc everything is working fine but on laptop when using the keyboard short cut it's protecting everything and ignoring the exemptions. Very puzzled at that tbh.
Is there a way to ignore the hidden sheet. The very hidden sheet is codename sheet4.
Thanks
 
Upvote 0
If that's the case, just test for the codename

VBA Code:
For Each ws In ActiveWorkbook.Worksheets
    If ws.CodeName <> "sheet4" Then

After this line do the protect
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top