Customized Menu in only one workbook

[rpm]

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!

 

[Anne Troy]

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]

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.

 

[rpm]

Thanks Dreamboat! Works great.
rpm