Have had a quick look and not sure if this will solve my problem as this
would assume that you are opening a specific workbook and using the code in
there. I will be opening a new workbook each time a report is run in visio
and saved to excel so it won't be based on a paricular workbook. If I do what
you suggest - can I have this template still open up on "any" workbook
automatically so that the users can use the buttons regardless of what
spreadsheet they are using?

I am not so bothered if the toolbar with the macros is always sitting there
as I can just tell the users to ignore it for any other spreadsheets. It is
easier than getting them to save this file there and that file somewhere else
and open this menu etc etc.

Hope the question is clear

Thanks
Bec

"Bec" wrote:

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