I am not totally sure I understand the problem but it rings a distant bell. Do you use Outlines? Probably not. If you did, you would have noticed that these also seize to function when you turn on sheet protection. The way MS designed the sheet protection does not work for me. (It'd be curious to learn whether it works for anyone out of the box). I need to intervene via VBA at startup like so:
Sub lockSheets()
Dim wsh As Worksheet
' This macro loops thru all worksheets in this workbook and looks for a name "LockMe". If
' the name exists and evaluates to TRUE, the sheet is first unprotected to enableOutlining,
' which is the ability of a user to expand[+] or collapse[-] grouped rows and columns when
' the sheet is protected (a setting that, bizarrely, does not survive a session and needs
' to be reactivated before each new session), before being protected (again).
' The nuisance of needing to redo enableOutlining before each session has the advantage
' to ensure that sheets which are intended to be locked will be locked even when a user
' unprotected the sheet in the previous seesion (and forgot to protect it again).
' To leave a sheet permanently unprotected, either set its "LockMe" to FALSE or delete the
' name altogether.
On Error GoTo Cleanup
Application.ScreenUpdating = False
For Each wsh In ActiveWorkbook.Worksheets
Dim lockMe As Variant
lockMe = wsh.Evaluate("LockMe")
If IsError(lockMe) Then lockMe = False
If lockMe Then
wsh.Unprotect
wsh.EnableOutlining = True
wsh.Protect UserInterfaceOnly:=True, AllowFormattingCells:=True
End If
Next wsh
Cleanup:
Application.ScreenUpdating = True
End Sub
Key to this code is wsh.EnableOutlining = True. I suspect that might afflict your efforts too.
