getting macros to work in all workbooks

i have a workbook that i use as a template. i am using the save as option in order not to change any of the data on it. However, whenever i save it under a different name the macros that i have set up seem to want to open the original book and do the selected action in there. is there a way to get the macros to work in all books without having to refer to the original file? i can get the macros to work if i reassign the buttons, but this is quite annoying. any light on the matter would be great!

andy,

Here's my standard instructions/reply:

The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed.
Follow these instructions and example code.

In the workbook's Thisworkbook object code module, place the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub

Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThe
CreateCommandbar
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub

In a regular code module, place the following:

Dim myBar As CommandBar
Dim myButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With

End Sub

Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub

Sub SayHello()
MsgBox "Hello there"
End Sub

You can add as many buttons or other menu items as you like.

HTH,
Bernie
MS Excel MVP

"andy" wrote in message
...
i have a workbook that i use as a template. i am using the save as option
in order not to change any of the data on it. However, whenever i save it
under a different name the macros that i have set up seem to want to open
the original book and do the selected action in there. is there a way to get
the macros to work in all books without having to refer to the original
file? i can get the macros to work if i reassign the buttons, but this is
quite annoying. any light on the matter would be great!