VBA to Protect Worksheet & allow formatting columns but keep certain columns hidden

[Chris Macro]

I am trying to figure out a way to protect my worksheet with VBA but include the following criteria:

• Not allow users to unhide columns or rows
• Allow users to format columns and rows (ie readjust column width, etc...)

When I enable my sheet protection with "Allow Column Formatting" checked, I can adjust columns widths but can also unhide columns that I want to remain hidden. Any thoughts on how to get around this?

Thanks for your help!

 

[JRS1010]

The following code will disable users from hiding columns and rows. If you have hidden columns or rows that you don't want to be unhidden, then this code can be tweaked to accommodate that, would just need to know which rows and/or columns.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    For Each Column In ActiveSheet.Columns
        If Column.ColumnWidth = 0 Then
            MsgBox "Hiding columns has been disabled for this workbook"
            Application.Undo
        End If
    Next
    For Each Row In ActiveSheet.UsedRange.Rows
        If Row.RowHeight = 0 Then
            MsgBox "Hiding rows has been disabled for this workbook"
            Application.Undo
        End If
    Next
End Sub

And you would paste this into the worksheet code for the sheet in question.