Forum Discussion

mpellegrino's avatar
mpellegrino
Copper Contributor
Feb 12, 2023

hiding and unhiding rows on based on cell value on a protected sheet

This will be an end-user spreadsheet.  There are certain cells they need to access, the rest need to be locked, but readable.  I have code that hides/unhides rows based on a cell value in that sheet.  It works fine.  But when I go to set up the cells the user can access and then protect the sheet, it won't work.  I can't find a work-around.  Anyone have any ideas?  

- I tried unprotect/protect in the code, that didn't work either.

Excel
Macros and VBA
  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Feb 14, 2023

    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.

Resources