Customized Menu in only one workbook

rpm

New Member
Joined
Aug 28, 2002
Messages
11
I created a customized menu which is applicable to only one workbook. Now when I open any unrelated workbook, the customized menu appears. How do I limit this menu to only one workbook?
Thanks!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
By creating the menu using VBA code.

Put something like this in the Workbook_Open:

Sub AddNewMenuItem()

Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Dim CmdBarMenuItem As CommandBarControl
'
' Point to the Worksheet Menu Bar
'
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
'
' Point to the Tools menu on the menu bar
'
Set CmdBarMenu = CmdBar.Controls("Tools")
'
' Add a new menu item to the Tools menu
'
Set CmdBarMenuItem = CmdBarMenu.Controls.Add
'
' Set the properties for the new control
'
With CmdBarMenuItem
.Caption = "New Item"
.OnAction = "'" & ThisWorkbook.Name & "'!MyMacro"
.Tag = "SomeString"
End With

End Sub


and something like this on the workbook close:

Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl

Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("Tools")
CmdBarMenu.Controls("New Item").Delete

Copied from Chip Pearson's site.
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
On 2002-09-12 13:04, rpm wrote:
I created a customized menu which is applicable to only one workbook. Now when I open any unrelated workbook, the customized menu appears. How do I limit this menu to only one workbook?
Thanks!

If you want something simple:
Private Sub Workbook_Open()
Application.CommandBars("custom").Visible = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("custom").Visible = False
End Sub

"custom" is the name of your menu.
Your menu will be available for all workbooks.
 

Forum statistics

Threads
1,143,915
Messages
5,721,520
Members
422,369
Latest member
redinator

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
Top