Thanks for both replies. Not brilliant at code but will have a look through
and see what I can figure out. Thanks for the help!

Cheers
Bec

"Bernie Deitrick" wrote:

Bec,

Attaching toolbars is problematic.

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

'These two are optional, use if you want to only use the toolbar
'with the file where the code is located
'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



"Bec" wrote in message
...
I have created a few macros and added them to a toolbar but I can't for the
life of me get them to work on another computer. I have "attached" the
toolbar so it stays with the spreadsheet. And I have added the macros into
the personal.xls file and attached them to the toolbar buttons.

I then copy the template to another persons pc into the their template drive
and add the personal.xl into their startup.

But...when I go to use the buttons an error message comes up saying the
template (with the macros) can't be found. When I go and see how the macros
are added to the toolbar they seem to have file locations from the original
pc even though I added them as directly from the personal.xls.

I have read lots of posts and am trying to figure this out but it is just
not working.

(please note that at no time will the users be clicking on the template but
they will be just using the toolbar as it opens in any excel file - this is
because I am running a report from excel which does not run from the excel
spreadsheet)

I have tried to include as much inof as possible but apologies if this is
confusing.

Thanks in advance!!!!!!