Hide/Unhide columns when protecting/unprotecting sheets

[Chewyhairball]

Hi

I have a protected sheet where column AA and AB are for comments. I would like to be able to type into this column and when the sheet is protected the columns are hidden.
They should remain hidden until the sheet is unprotected.

I dont want a button or toggle switch or anything like that. I would like the columns to be hidden or unhidden based on if the sheet is protected or unprotected.

thanks

Rory

 

[rollis13]

Have a try with what I came up with. Your columns will Hide/UnHide only when you select a different cell since I'm using event Worksheet_SelectionChange to trigger the check of the sheet's state Protected/UnProtected but I think this won't be a problem. The protection can be managed by another macro or manually. Paste the macro in the sheet's module and see if you can integrate it into your project.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.UnProtect
        Columns("AA:AB").EntireColumn.Hidden = True
        ActiveSheet.Protect
    Else
        Columns("AA:AB").EntireColumn.Hidden = False
    End If
End Sub

 

[rollis13]

Glad having been of some help.

 

[Chewyhairball]

Have a try with what I came up with. Your columns will Hide/UnHide only when you select a different cell since I'm using event Worksheet_SelectionChange to trigger the check of the sheet's state Protected/UnProtected but I think this won't be a problem. The protection can be managed by another macro or manually. Paste the macro in the sheet's module and see if you can integrate it into your project.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.UnProtect
        Columns("AA:AB").EntireColumn.Hidden = True
        ActiveSheet.Protect
    Else
        Columns("AA:AB").EntireColumn.Hidden = False
    End If
End Sub

Brilliant. Works exactly as I need