assign macro to tollbar buttons(excel2003)

[shamsam1]

i have created macro in module.i am following custom methods to assign macro to a toolbar buttons and it works fine.But then i copy that excel to different folder then macros assigned to buttons don't run it shows error again i need to assign macros to button then it runs,If i do save as excel sheet to particular folder macro assigned to custom buttons runs perfectly.
is there any vba code to assign macro to custom buttons :think:so that even if i copy and paste the excel it should work.excel(2003)

[Bob Phillips]

I prefer to build the menu/toolbar on the fly, and tear it down at the end, but you could do this

Tools>Customize, select the toolbar and click the Attach button.

[shamsam1]

i follow the same method.but Problem come only when i copy the excel to different location..its shows error .
But again i need to follow Tools>Customize, select the toolbar and click the Attach button. if i move the excel

[Bob Phillips]

Then build it on workboo open as I do.

Here is an example



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.

[shamsam1]

Perfect thanks for the help:bow: