Not allow users to hide columns in protected sheet

[gubertu]

Hi all,

I have a protected sheet, where I want the users to be able to change the size of the columns but not to hide them.

In the option "Protect sheet" I can unable "Format columns", but If I do that, the users will not be able to change the size of the columns.

Is there a code for this?

Thanks in advance!

 

[offthelip]

you could allow the users to hide the columns but unhide tehm when they deactivate the sheet by putting this in the worksheet deactivate event. ( I like the sneaky way this works!!)

Private Sub Worksheet_Deactivate()
Columns("A:Z").Hidden = False
End Sub

 

[VBE313]

Sub lockActiveSheetFormatColumns ()
ActiveSheet.Protect Password:="EnterPasswordHere", AllowFormattingColumns:=True
End Sub

 

[gubertu]

Thanks for the code! I think this is close to what I need.

It is possible that when I change to a sheet, is is forbidden to hide the columns?

Thanks!

you could allow the users to hide the columns but unhide tehm when they deactivate the sheet by putting this in the worksheet deactivate event. ( I like the sneaky way this works!!)

Private Sub Worksheet_Deactivate()
Columns("A:Z").Hidden = False
End Sub

 

[offthelip]

I had a further thought : you will also need to put this code in the workbook before save event, to prevent them savign the workbook with columns hidden:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For i = 1 To Worksheets.Count
 Worksheets(i).Columns("A:Z").Hidden = False
Next i
End Sub

 

[gubertu]

This code is great! I think it is enough for what I need. When someone tries to save the WB with a hidden columns, the columns just are unhidden right?

Thanks!

I had a further thought : you will also need to put this code in the workbook before save event, to prevent them savign the workbook with columns hidden:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For i = 1 To Worksheets.Count
 Worksheets(i).Columns("A:Z").Hidden = False
Next i
End Sub