Disabling Menu Item

[Bill]

I've seen posts related to code needed to disable menu items, but am still a
bit confused. I want to disable the "Unprotect Sheet" menu item once the
workbook is open. What would be the particular coding for that, and how do
you find the proper ID number to use? I'm using Excel 97 still believe it or
not. Thanks in advance.

 

[JE McGimpsey]

you can find the ID by entering this in the Immediate Window (with the
worksheet protected - if it's unprotected, substitute "Protect
Sheet..."):

?Commandbars(1).Controls("Tools").Controls("Protection").Controls("Unprot
ect Sheet...").Id

I get 893.

You can then put this in the ThisWorkbook code module:

Private Sub Workbook_Activate()
DisableUnprotect
End Sub

Private Sub Workbook_Deactivate()
EnableUnprotect
End Sub

Private Sub Workbook_Open()
DisableUnprotect
End Sub


And this in a regular module

Public Sub DisableUnprotect()
With CommandBars.FindControl(Id:=893)
.Enabled = False
.Visible = False
End With
End Sub

Public Sub EnableUnprotect()
With CommandBars.FindControl(Id:=893)
.Enabled = True
.Visible = True
End With
End Sub

 

[Bill]

Thanks JE. I'm a bit of a hack, so I hope you don't mind a follow up
question or two. I've never used the ThisWorkbook code module - what is the
purpose of that? Also, if my company is using Excel 97, is it safe to assume
the other users who will open this workbook will have the Unprotect Sheet...
I of 893 also? Thanks again for your help.

Bill

 

[JE McGimpsey]

I've never used the ThisWorkbook code module - what is the purpose of
that?
http://www.mcgimpsey.com/excel/modules.html

Also, if my company is using Excel 97, is it safe to assume the other
users who will open this workbook will have the Unprotect Sheet... I
of 893 also?

In all current versions it is. I suppose that it's possible it will
change, but given that it would break existing code, it's unlikely.

You could check for

Commandbars(1).Controls("Tools").Controls( _
"Protect").Controls("Unprotect Sheet...").Id

but if the active sheet is not protected, it will fail (since the
caption is changed to "Protect Sheet..."). It will also fail if the user
has moved the control to another location.

 

[Ron de Bruin]

More ID information you can find here

http://support.microsoft.com/default.aspx?scid=kb;en-us;159466&Product=xlw
XL97: List of ID Numbers for Built-In Command Bar Controls

http://support.microsoft.com/default.aspx?scid=kb;en-us;162814&Product=xlw
XL97: Sample Macros to Return ID for a CommandBar Control

http://support.microsoft.com/default.aspx?scid=kb;en-us;166755&Product=xlw
XL97: WE1183: "Customizing Menu Bars, Menus, and Menu Items"

http://support.microsoft.com/default.aspx?scid=kb;en-us;159619&Product=xlw
XL97: Sample Macros for Customizing Menus and Submenus

http://support.microsoft.com/default.aspx?scid=kb;en-us;162878&Product=xlw
XL97: Sample Macros That Customize and Control Shortcut Menus

 

[Bill]

Thanks again to everyone for their help. Works perfectly!