Excel – Lock cells in Excel, but still allow to show/hide

excelvba

I'm building an excel sheet. The excel sheet will show or hide certain rows based on what is selected in a different cell. For example (pseudo).

if B6 = "Yes", Show row 7, Else Hide row 7.

I want to lock cells so they can't change the titles and headings, but still be able to hide the rows under certain situations. When the cells are locked, my VBA code isn't able to hide or unhide rows.

Any help would be appreciated; I have searched for answers but nothing for my specific situation has been found.

Best Answer

Here's a routine that I've created a while back to lock everything, but allow as much functionality as possible. You can edit the arguments as you see fit. I was able to hide a row after running this.

Sub ProtectSheetsNoPassword()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        ws.Protect DrawingObjects:=True, _
                    Contents:=True, _
                    Scenarios:=True, _
                    AllowFormattingCells:=True, _
                    AllowFormattingColumns:=True, _
                    AllowFormattingRows:=True, _ 
                    AllowInsertingColumns:=True, _
                    AllowInsertingRows:=True, _
                    AllowInsertingHyperlinks:=True, _
                    AllowDeletingColumns:=True, _
                    AllowDeletingRows:=True, _
                    AllowSorting:=True, _
                    AllowFiltering:=True, _
                    AllowUsingPivotTables:=True
        'ws.Unprotect 'Uncomment if you want to unprotect the sheets
    Next
End Sub

The AllowFormattingRows:=True argument is the one you need to focus on for hiding rows.

Related Topic