Toolbar Buttons (macros) fail after filename change

[Guest]

I require a toolbar of buttons which are assigned to workbook macros to work when the filename changes. What do I have to do to ensure this? Currently they work until I change the filename then they look to the wrong place...

 

[Greg]

My preferred option is to recreate the entire toolbar programmatically
each time the workbook is opened. This requires a lot more code however.
The following is the simplest method of solving your problem that I am
aware of. It assumes that none of the controls are msoControlPopup type
- i.e. menu type that have controls of their own. Assumed is that the
toolbar is named "Custom 1" with minimal testing:

Private Sub Workbook_Open()
Dim CB As CommandBar
Dim Ctl As CommandBarControl
Dim Pos As Integer
Dim txt As String

Set CB = Application.CommandBars("Custom 1")
On Error Resume Next
For Each Ctl In CB.Controls
If Ctl.BuiltIn = False Then
txt = Ctl.OnAction
Pos = InStrRev(txt, "!")
txt = Right(txt, Len(txt) - Pos)
Ctl.OnAction = txt
End If
Next
On Error GoTo 0
End Sub