Hide menu items

jgoulart

Board Regular
Joined
Feb 16, 2002
Messages
62
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)<pre>

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</pre>

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


_________________<font color = green> 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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
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