Macros point to workbook with previous name

Hi codequest,

Okay, fooled around with this a bit, and you're quite right.  If you create a toolbar by just adding buttons and assigning macros, it makes it workbook specific.  Save it, and they still refer to the old book.  Curiously, though, if you make a menu through the same manner, they will transport just fine.  Go figure that.

Personally, I prefer to create all my menus and toolbars automagically via code.  I've posted some code below, which will add a button to a toolbar via code on the fly when a workbook is activated, and removes it when you activate another workbook.  The code below adds a button to the Standard Toolbar, but you can easily use a different toolbar (by changing that name) or even create your very own.  The benefits of this approach is that you don't have to worry about toolbars becomming corrupted, and you can ensure that they always look exactly as you want.

Here's the code:

***

Option Explicit

Private Sub Workbook_Deactivate()
'To delete a toolbar button when workbook is deactivate
Call DeleteButton
End Sub

Sub Workbook_Activate()
'To add a toolbar button to a menu when workbook is activated
Dim cb As CommandBar
Dim sCBarName As String
Dim sCaption As String
Dim sMacroName As String

'Set MenuBar, Button Caption and Macro here
sCBarName = "Standard"
sCaption = "Test Macro"
sMacroName = "Test"

'Delete any instance of the button that already exists
Call DeleteButton

'Link to commandbar
Set cb = Application.CommandBars(sCBarName)

'Add button
With cb
    With .Controls.Add(Type:=msoControlButton, before:=.Controls.Count + 1)
        .Style = msoComboLabel
        .FaceId = 59 'Change button image here
        .OnAction = sMacroName
        .Caption = sCaption
    End With
End With
End Sub

Sub DeleteButton()
'To delete a toolbar button
Dim cbc As CommandBarControl
Dim lControl As Long
Dim sCBarName As String
Dim sCaption As String

'Set Menu Bar Name here
sCBarName = "Standard"
sCaption = "Test Macro"

'Ignore errors
On Error Resume Next

'Check each control on the toolbar and delete it if the caption matches
For lControl = Application.CommandBars(sCBarName).Controls.Count To 1 Step -1
    Set cbc = Application.CommandBars(sCBarName).Controls(lControl)
    If cbc.Caption = "Test Macro" Then
        cbc.Delete
    End If
Next lControl

'Resume error handling
On Error GoTo 0

End Sub

Sub test()
'Illustration purposes only
MsgBox "test"
End Sub

***

The first two procedures go in the ThisWorkbook module, and the other two go in a standard module.  Remember that when you change the menu names, you must change them in both the Workbook_Activate AND the DeleteButton procedures.

HTH,