Toolbar Buttons (macros) fail after filename change
 

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...

Toolbar Buttons (macros) fail after filename change
 

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!