Protecting hidden columns

[prima satria]

I have some columns to hide using vba code below :

Sub HIDE_COLUMNS()
Sheets("SUMMARY").Select
Columns("AN:AO").Select
Selection.EntireColumn.Hidden = True
End Sub

And also have a button to unhide the columns, with code below :

Sub UNHIDE_COLUMNS()
Sheets("SUMMARY").Select
Columns("AN:AO").Select
Selection.EntireColumn.Hidden = False
End Sub

But the problem is, still it can be unhide from the menu, click the right button from the selected columns header. I don't want to hide the excel's menu. Is there any code to protecting the hidden columns, so it can be accessed only if you click the "unhide" button.

Thx so much

 

[Andrew Poulsom]

Protecting the worksheet prevents the user from hiding/unhiding columns. You would need to unprotect/reprotect in your code.

 

[prima satria]

I'll be glad if you could provide the code to fix it.

Thx in advance

 

[Andrew Poulsom]

Try:

Sub HIDE_COLUMNS()
    With Sheets("SUMMARY")
        .Unprotect
        .Columns("AN:AO").EntireColumn.Hidden = True
        .Protect
    End With
End Sub

Sub UNHIDE_COLUMNS()
    With Sheets("SUMMARY")
        .Unprotect
        .Columns("AN:AO").EntireColumn.Hidden = False
        .Protect
    End With
End Sub

 

[prima satria]

Dear Mr. Andrew,

It won't work. The hidden columns still can be access from Excel's menu and/or by doing right click on columns header.

Thank you.

 

[prima satria]

Dear Mr. Andrew,

I'm really sorry, it works ! (I forgot to assign the macro on the button).

Thank you, thank you very much

(terima kasih in Indonesia).

 

[prima satria]

Mr. Andrew,

How if I do not want the sheets "summary" to protect, but only those hidden columns. Is it possible ? Is there any magic code ?

 

[Andrew Poulsom]

Sorry I don't know how to prevent a user from unhiding columns without protecting the worksheet.

 

[prima satria]

Thank you anyway for helping me so much