Toolbar with Macros

[bw]

I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list. This macros (and the items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1. I have simply dragged an item from the toolbar customize box to the Tools menu, and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2. I have saved the original workbook as a new workbook, then deleted everything in
the new workbook, including one of the Tools Menu items, and reassigned new macros
to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook. I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie

 

[Ron de Bruin]

One way to place a item in the tools menu

Run the macros in this events in the Thisworkbook module for example

Private Sub Workbook_Activate()
MenuBar_Item_Item
End Sub

Private Sub Workbook_Deactivate()
MenuBar_Item_Item_Delete
End Sub


Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItemTag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub

 

[bw]

Ron,

I haven't tried your suggestion yet. But this seems very confusing and a lot of effort to do
what I have asked. I didn't have to go through all this when I placed these items on the
Tools menu eight years ago, so I don't know why I have to now?

I'm clearly missing something important here, but I'm not sure what...

Bernie

 

[Ron de Bruin]

You posted in Programming so I give you a macro solution.

 

[Gord Dibben]

Bernie

Thanks for the feedback.

Ron's code will help you get started with creating Toolbars and Menus "on the
fly".

Gord