How to Disable Unhide Sheet menu option

[Guest]

How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook

 

[Guest]

Try this:-

Private Sub Workbook_Open()
With Application.CommandBars("Format")
With .Controls("&Sheet")
.Controls("&Unhide...").Enabled = False
End With
End With
End Sub

Remebering of course to to put the same routing in the before_close event
with false changed to true.

Mike

 

[JW]

1-Open your Visual Basic Editor (Alt+F11).
2-Make sure the Project window is visible. If it isn't, go to View--

Project Explorer.

3-Make sure that the properties window is visible. If it isn't, goto
View-->Properties Window.
4-In the Project Exolorer, find your workbook.
5-One at a time, select the sheets within that workbook that you don't
want the users to be able to unhide.
6-While the sheet is selected in the Project window, go down to the
Properties window and change the Visible property to
xlSheetVeryHidden.

This will cause any sheet set to xlSheetVeryHidden to not be displayed
in the Unhide Sheets dialog box. If no sheets are hidden other than
those set to xlSheetVeryHidden, then the Unhide Sheets menu option
will be grayed out.

 

[Ron de Bruin]

Hi

If your code must work also in non English versions use

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=891, Recursive:=True).Enabled = False
End Sub

More info here
http://www.rondebruin.nl/menuid.htm

 

[JE McGimpsey]

And if there's a possibility that "Worksheet Menu Bar" isn't the active
menubar (do all non-English versions use that name?), you can even use:

Application.Commandbars.ActiveMenuBar.FindControl( _
ID:=891, Recursive:=True).Enabled = False

 

[Ron de Bruin]

Hi J.E

You must use the English names for the command bars but the local names for the controls.
This is to make it easy <g>

So the combination English command bar name with the ID of the control is always working

 

[JE McGimpsey]

You must use the English names for the command bars but the local names for
the controls.
This is to make it easy <g>

Ah.. Makes perfect MSense.

So the combination English command bar name with the ID of the control is
always working

Thanks. I never assume that the user hasn't replaced the Worksheet Menu
Bar with a custom menu bar (since my apps do that all the time).

 

[Guest]

Hi,

Could you direct me to a list of these control Id's please.

Mike

 

[Guest]

Apologies I must learn to read more carefully, you have provided the link
with this information.

Mike