Sheets Protection Macro Not Working Properly

[JohnGow383]

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:

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

 

[Alex Blakenburg]

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

'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.

'Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets

 

[JohnGow383]

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

'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.

'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

 

[FryGirl]

If that's the case, just test for the codename

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

After this line do the protect

 

[JohnGow383]

Thanks

If that's the case, just test for the codename

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

After this line do the protect

Thanks