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

Sometimes when you try to attach a macro to a button you need to delare the
module that it is in. Say you created a module called RunMacro1 and the name
of the macro was Macro1 then you need to make sure the function's OnAction
event in the button's _click event would be....

..OnAction = "RunMacro1.Macro1"

HTH.

"Bec" wrote:

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

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

I have added the macros to the toolbar by first adding custom buttons and
then by "assigning" a macro to them. The macros have no "on action" command.
Have I added them the wrong way??

On another note:
I am used to work macros and templates and this is very very confusing. It
means if a user wants to use the template he must add it to his template
folder, add another file to his start folder and change macro setting etc
etc. Is this seriously how they expect a user to use a template? Is there no
way just to have everything in one template and have it running in the
background and have toolbar plus macros be accessible?????

Thanks again
Bec

"DaveO" wrote:

Sometimes when you try to attach a macro to a button you need to delare the
module that it is in. Say you created a module called RunMacro1 and the name
of the macro was Macro1 then you need to make sure the function's OnAction
event in the button's _click event would be....

.OnAction = "RunMacro1.Macro1"

HTH.

"Bec" wrote:

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

Personally, I'd create my own toolbar in the code and then assign the macros
to the buttons I create. An example of this is below from something I wrote
previously (PLease note that this is a full owrking copy of my code. The
toolbar related stuff is at the bottom). I apologise for the lack of
comments, but hopefully you'll be able to make it out. If not let me know and
I'll comment it for you.

Please note I've blanked out file locations so it's easier to understand...

Private Sub Workbook_Open()

' This code grabs the Teams database and copies the Team data into this
Workbook. The data is copied into Sheet1 in range
' A100 to Bx (Where x = intrecordcount). It then does the Data Validation on
that sheet. We then copy the data from Sheet1
' into Sheet 3 and does exactly the same thing.

On Error GoTo errAmendment

Dim strUserName As String

strUserName = Environ("USERNAME")

If strUserName = "dogden" Or strUserName = "dmole2" Then
GoTo EndOfScript
End If

Dim dboTeamsDb As Database
Dim rcsRecords As Recordset
Dim strDbPath As String
Dim intRecordCount As Integer
Dim vntData As Variant

' This is the path for the database lookup. This will need to change if the
database location ever changes.

strDbPath = "{PATH}"

Worksheets(1).Select
ActiveSheet.Unprotect "{PASSWORD}"
Rows("100:500").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Sheets(2).Select
ActiveSheet.Unprotect "{PASSWORD}""
Rows("100:500").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Sheets(3).Select
ActiveSheet.Unprotect "{PASSWORD}""
Rows("100:500").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Worksheets(1).Select

Rows("100:500").Select
Selection.EntireRow.Hidden = False

Range("A100:B500").Select
Selection.ClearContents

' Open up a connection to the Teams database.

Set dboTeamsDb = OpenDatabase(strDbPath & "Teams.mdb")
Set rcsRecords = dboTeamsDb.OpenRecordset(Name:="tblTeams to Departments",
Type:=dbOpenDynaset)

With rcsRecords
.MoveLast
intRecordCount = .RecordCount
.MoveFirst
End With

vntData = rcsRecords.GetRows(intRecordCount)

' The data is not pasted into Sheet1

With Worksheets(1)
.Range("A100:B" & 99 + intRecordCount).Value =
Application.Transpose(vntData)
.Select
End With

dboTeamsDb.Close

' db connection now closed. The rest of this is purely Excel formatting and
stuff.

' Sorting the data A to Z and clearing column B of the Group.

Range("A100:A" & 99 + intRecordCount).Select
Selection.Sort Key1:=Range("A100"), Order1:=xlAscending, Header:=xlNo
Range("B100:B" & 99 + intRecordCount).Select
Selection.ClearContents

' Data Validation for the teams cells.

Range("D2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$100:$A$" & 99 + intRecordCount
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Team Name"
.ErrorTitle = "Error"
.InputMessage = "Please select the team name from the list."
.ErrorMessage = "Please check what you have typed or selected as this
appears to be wrong."
.ShowInput = True
.ShowError = True
End With

' Copying that validation to all of the cells.

Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault

' By copying the borders are screwed, so this tidies them up. I have hacked
out all of the crap here and only change back what
' was altered. Recording a macro would have tonnes of lines here as it sets
all of the properties.

Range("A2:G16").Select
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With

' Copying the base data to move to another sheet for data validation purposes.

Rows("100:500").Select
Selection.Copy

' Sheet 2 selected.

Sheets(2).Select
Range("A100").Select
ActiveSheet.Paste

' Data Validation time.

Range("D2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$100:$A$" & 99 + intRecordCount
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Team Name"
.ErrorTitle = "Error"
.InputMessage = "Please select the team name from the list."
.ErrorMessage = "Please check what you have typed or selected as this
appears to be wrong."
.ShowInput = True
.ShowError = True
End With

' Copy and paste the validation and sorting the borders out.

Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault

Range("A2:G16").Select
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With

' Hiding the data for tidiness sake and protectiong the sheet from user
error. How we love making allowances for mistakes!!!

Rows("100:500").Select
Selection.EntireRow.Hidden = True

ActiveSheet.Protect "{PASSWORD}"

Range("A1").Select

' OK copying the data and doing the same as before. Look above if you're
unsure.

Sheets(1).Select
Rows("100:500").Select
Selection.Copy

Sheets(3).Select
Range("A100").Select
ActiveSheet.Paste

Range("D2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$100:$A$" & 99 + intRecordCount
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Team Name"
.ErrorTitle = "Error"
.InputMessage = "Please select the team name from the list."
.ErrorMessage = "Please check what you have typed or selected as this
appears to be wrong."
.ShowInput = True
.ShowError = True
End With

Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault

Range("A2:G16").Select
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With

Rows("100:500").Select
Selection.EntireRow.Hidden = True

ActiveSheet.Protect "{PASSWORD}"

Range("A1").Select

Sheets(1).Select

Rows("100:500").Select
Selection.EntireRow.Hidden = True

ActiveSheet.Protect "{PASSWORD}"

Range("A1").Select

' OK so we got a bit smarter here and built my own toolbars...

' Please note that most of the variables I now use are declared at the
worksheet level as I need to use them later.

Dim cmbBars As CommandBar

intBars = 0

' This here only allows you unhide toolbars if something went wrong!

'For Each cmbBars In Application.CommandBars
' If cmbBars.Visible = False Then
' If MsgBox(cmbBars.Name & " Enable?", vbYesNo) = vbYes Then
' cmbBars.Enabled = True
' End If
' End If
'Next cmbBars

' Find the amount of toolbars and gets rid of my toolbar if it exists.

For Each cmbBars In Application.CommandBars
If cmbBars.Visible = True Then
If cmbBars.Name = "DaveO Toolbar" Then
cmbBars.Delete
End If
intBars = intBars + 1
End If
Next cmbBars

ReDim aryBarArray(intBars) As CommandBar

intBars = 0

' Set the array with the commandbar objects.

For Each cmbBars In Application.CommandBars
If cmbBars.Visible = True Then
intBars = intBars + 1
Set aryBarArray(intBars) = cmbBars
End If
Next cmbBars

intTotalBars = intBars

intBars = 0

'Hiding all of the commandbars.

Do While intBars < intTotalBars
intBars = intBars + 1
aryBarArray(intBars).Enabled = False
Loop

intBars = 0

' Create my own tool bar.

With Application.CommandBars.Add("DaveO Toolbar", msoBarTop, False, True)
.Visible = True
With .Controls
With .Add(msoControlButton)
.Caption = "Save"
.FaceId = 3
.OnAction = "SaveFileOff.SaveFileOff"
End With
With .Add(msoControlButton)
.Caption = "Send To Dialler Management"
.FaceId = 363
.OnAction = "SendFileViaMail.SendFileViaMail"
End With
End With
End With

Exit Sub

errAmendment:

MsgBox Err.Number & " " & Err.Description

EndOfScript:

End Sub
********************************
I now have 2 modules which hold the macros I run when the buttons are
clicked...

Module = SaveFileOff

Sub SaveFileOff()
'
' SaveFileOff Macro
' Macro recorded 12/04/2005 by Dave Ogden


fctCheckForeNames
fctCheckSurnames
fctEvoGuys

If MsgBox("Are you happy with all of the alterations you have made?",
vbYesNo, "Is everything completed?") = vbYes Then
Application.Dialogs(xlDialogSaveAs).Show "*.xls"
End If

End Sub

Module = SendFileViaMail

Sub SendFileViaMail()
'
' SaveFileOff Macro
' Macro recorded 12/04/2005 by Dave Ogden

fctCheckForeNames
fctCheckSurnames
fctEvoGuys

If MsgBox("Are you happy with all of the alterations you have made?",
vbYesNo, "Is everything completed?") = vbYes Then
Application.Dialogs(xlDialogSendMail).Show "Dialler Management",
"Concerto Agent Amendment Form."
End If

End Sub

HTH.



"Bec" wrote:

I have added the macros to the toolbar by first adding custom buttons and
then by "assigning" a macro to them. The macros have no "on action" command.
Have I added them the wrong way??

On another note:
I am used to work macros and templates and this is very very confusing. It
means if a user wants to use the template he must add it to his template
folder, add another file to his start folder and change macro setting etc
etc. Is this seriously how they expect a user to use a template? Is there no
way just to have everything in one template and have it running in the
background and have toolbar plus macros be accessible?????

Thanks again
Bec

"DaveO" wrote:

Sometimes when you try to attach a macro to a button you need to delare the
module that it is in. Say you created a module called RunMacro1 and the name
of the macro was Macro1 then you need to make sure the function's OnAction
event in the button's _click event would be....

.OnAction = "RunMacro1.Macro1"

HTH.

"Bec" wrote:

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

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

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

Yes, your question is clear. You can either add the workbook to their XLStart folder, or you can
save it as an add-in, and then add it to their set-up using Tools / Add-ins... and browsing to find
it.

HTH,
Bernie
MS Excel MVP


"Bec" wrote in message
...
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!!!!!!