Protecting hidden columns

prima satria

Board Regular
Joined
Nov 3, 2007
Messages
63
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Protecting the worksheet prevents the user from hiding/unhiding columns. You would need to unprotect/reprotect in your code.
 
Upvote 0
Try:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top