Renaming excel files which have embedded macros

[vic]

I have an excel (version 2002) file in which I have created a number of macros. I have also created a toolbar that contains buttons that execute some of these macros. However, I have discovered a problem which appears to be triggered when I have the workbook open and then choose to save it using a different name. When I close excel and try to open the original workbook once again, the macro buttons in the tool bar, when clicked, seem to want to open up the the renamed excel file that I saved a few moments ago. The macros by the way, cause excel to jump to a different worksheet in the workbook a either hide or display certain columns.

I originally had the toolbars attached to the excel workbook, but I have since detached them to see if this had any effect, but i still have the same problem. Does anyone know why the macro buttons in the original version of the file now want to access the renamed file, and also what I can do to get around this. The reason I ask is that the workbook can be sent to other users who may choose to rename the file.

[Bob Phillips]

build the toolbar dynamically



Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub



To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.