I have an Excel workbook with many sheets that may lock and unlock programmatically.
WorkSheet.ProtectContents did not seem to be updating unless the sheet was activated, so I started throwing this into my functions that use that property.
However, I refactored a bit, and the "for each" loop in the code below quit working if I activate the sheet. My lock and unlock subroutines loop fine with sheetObj.activate uncommented.
This does not work and gets stuck on sheet1
Public Function getLockStatus() As Boolean
Dim sheetObj As Worksheet
For Each sheetObj In ThisWorkbook.Worksheets
sheetObj.Activate
Debug.Print sheetObj.Name & ": is locked: " & sheetObj.ProtectContents
If sheetObj.ProtectContents = False And sheetObj.Name <> "CSV Compatible" Then
Set sheetObj = Nothing
getLockStatus = False
Exit Function
End If
Next sheetObj
Set sheetObj = Nothing
getLockStatus = True
End Function
but the following code works:
Public Function unlockSheets() As Boolean
On Error GoTo doh:
Dim sheetObj As Worksheet
For Each sheetObj In ThisWorkbook.Sheets
sheetObj.Activate
If sheetObj.ProtectContents = True And sheetObj.Name <> "CSV Compatible" Then
sheetObj.Unprotect Password:="somestring"
End If
Next sheetObj
Set sheetObj = Nothing
unlockSheets = True
Exit Function
doh:
Set sheetObj = Nothing
Debug.Print "Failed to unlock sheets."
unlockSheets = False
End Function
You are fighting a lion that should be in a cage. Don't think of getting a longer spear. Instead, find out why the cage is left open.
The Protection object has a property UserInterfaceOnly. If you create the protection with this property set to True your code can access the sheet without the protection needing to be lifted. The problem is that this property can't be saved. It expires with the current session.
Therefore the protection object should be created with the Workbook_Open event. So, at the Open event remove the protection and set a new protection, 'UserInterfaceOnly = True`, and all the problem you are discussing above won't happen.