G
Guest
How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook
sheets in my excel workbook
G
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
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
J
JW
1-Open your Visual Basic Editor (Alt+F11).
2-Make sure the Project window is visible. If it isn't, go to View--
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.
2-Make sure the Project window is visible. If it isn't, go to View--
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.
R
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
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
J
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
menubar (do all non-English versions use that name?), you can even use:
Application.Commandbars.ActiveMenuBar.FindControl( _
ID:=891, Recursive:=True).Enabled = False
R
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
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
J
JE McGimpsey
Ron de Bruin said:
Ah.. Makes perfect MSense.
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).
G
Guest
Hi,
Could you direct me to a list of these control Id's please.
Mike
Could you direct me to a list of these control Id's please.
Mike
G
Guest
Apologies I must learn to read more carefully, you have provided the link
with this information.
Mike
with this information.
Mike