Excel protection, need to enable hide/unhide for users while protected

I have a routine which protects all cells in the workbook which contain formulas. I need to add to this routine an instruction to enable the user to hide/unhide. At the moment, if protection is enabled, the user cannot hide/unhide columns and rows.
Thank you!
JohnD
Sub prot()
Dim rng As Range
Dim prot As Range
Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        If ws.ProtectContents = True Then ws.Unprotect
        ws.Cells.Locked = False
        On Error Resume Next
        Set prot = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
        On Error GoTo 0
        If Not prot Is Nothing Then
            prot.Locked = True
            For Each rng In prot
                If rng.Parent.ProtectContents = False Then rng.Parent.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
            Next
        End If
        Set prot = Nothing
    Next
 
End Sub

Open in new window

LVL 1
John DarbyPMAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
I assumed you wan to give user the option of hide/unhide the rows even if the sheet is protected, If the answer is yes then use the following code.
Saurabh...

Sub prot() 
Dim rng As Range 
Dim prot As Range 
Dim ws As Worksheet 
  
    For Each ws In ThisWorkbook.Worksheets 
        If ws.ProtectContents = True Then ws.Unprotect 
        ws.Cells.Locked = False 
        On Error Resume Next 
        Set prot = ws.Cells.SpecialCells(xlCellTypeFormulas, 23) 
        On Error GoTo 0 
        If Not prot Is Nothing Then 
            prot.Locked = True 
            For Each rng In prot 
                If rng.Parent.ProtectContents = False Then rng.Parent.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingColumns:=True,AllowFormattingRows:=True
            Next 
        End If 
        Set prot = Nothing 
    Next 
  
End Sub

Open in new window

0
 
John DarbyPMAuthor Commented:
Thank you for the below statements!

AllowFormattingColumns:=True,AllowFormattingRows:=True
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.