Hide menu items

[jgoulart]

How can I write vba code to hide different menu items? I want to make the "Send to" selection under "File" hidden or grayed out. I also want to gray out "Macro" under "Tools".

John

[Corni]

The menu items which are part of Excel application cannot be controlled programatically for permanent modification. The only option available, which is relatively limited in extent is the Customize menu item, which allows a reduced level of customization to respond to personal preferences and working habits.
The possibility of disabling by code menu items exist only for This Workbook macros.

[ This Message was edited by: corni on 2002-03-05 19:05 ]

[ This Message was edited by: corni on 2002-03-06 19:44 ]

[viper]

This is what I did from the help here as well. Open VB editor and under General Declarations for This Workbook type the following:
Dim CB As CommandBar
Dim C As CommandBarControl
Dim ID

Then insert a module in This Workbook and type the following:

Sub Disable_RightClick_Toolbar()
ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = False
Next
Application.CommandBars("Toolbar List").Enabled = False
Application.CommandBars("Visual Basic").Enabled = False

End Sub

Then insert a second one and type the following:

Sub Enable_RightClick_ToolBar()

ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = True
Next
Application.CommandBars("ToolBar List").Enabled = True
Application.CommandBars("Visual Basic").Enabled = True

End Sub

Then in This Workbook Open event type:
Disable_RightClick_Toolbar

and in This Workbook Deactivate() type:
Enable_RightClick_Toolbar.

This worked great for me but their is one flaw and I hope whoever else reads this can help me fix this. But after you do the above and then save and re-open the file, the macro will be greyed under the tools menu, but if you leave that file open and then open a second file and then close it the macro will be enabled again. I haven't figured out how to stop that from happening but as long as the user doesn't know about the second file then your macro will remain greyed. As far as the other menu item I don't know but maybe this will give you a head start in modifying the code for the other item.

HTH

[viper]

Forgot to mention. For both of the module inserts you will need to put the following before the code as well as in the General Declarations for This Workbook:
Dim CB As CommandBar
Dim C As CommndBarControl
Dim ID

[Mark O'Brien]

For graying out the File|Send To, use this code (you don't need to set the commandbar or the first popup, but I've done it as an example)

  Dim CmdBar As CommandBar

  Dim CmdBarControl As CommandBarPopup

  Dim SendToControl As CommandBarPopup

  

  Set CmdBar = Application.CommandBars("Worksheet Menu Bar")

  Set CmdBarControl = CmdBar.Controls("&File")

  Set SendToControl = CmdBarControl.Controls("Sen&d To")



'Remember to set this to True before exiting Excel

  SendToControl.Enabled = False

For the Tools|Macro button do the same except put in "&Tools" instead of "&File"
and "&Macro" instead of "Sen&d To".

***Remember to reset these values to True before exiting Excel.

Cheers


_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-03-04 22:40 ]

[ This Message was edited by: Mark O'Brien on 2002-03-04 22:41 ]