Probelm with macros assigned to a custom toolbar

[Learner123]

I am having a probelm with macros assigned to a custom toolbar. I am using excel 2003.

1. I have created several macros in a workbook named "PLAN"

2. I then created a custom toolbar.

3. Through the customize dialog box, I assign the Macros to the custom tool bar button.

Everything work fine UNTIL...

If I do a SAVE AS for the workbook "PLAN" and call it something else like "PLAN2" and then close "PLAN2" and reopen "PLAN" the name of the macro assigned to the custom button has change

Where as before it was simply "PLAN"
now it is C:\Mydocuments\Test2.xls'!PLAN.
Since I have the workbook "PLAN" open and "PLAN2" is closed, excel now wants to open up "PLAN2" to access the macro assigned to that button.

I am creating a custom toolbar that will go with an excel workbook that I use as a template and will often resave it with different names.

I would like excel not to go looking in other workbooks for these macros since copies of the macros exist in the module attached to the workbook.

Every time I do a Save As the assigned macro gets renamed. I am not interested in making changes to Personal.xls, as other individuals will be using the workbook...

Does anyone have any ideas?

 

[Gary McMaster]

Some of your symptoms are listed here:

http://www.jkp-ads.com/Articles/DistributeMacro04.asp

Sounds like it's an attached toolbar that needs to be removed when the workbook is closed according to the above.

Gary

 

[John Davis]

I am having a probelm with macros assigned to a custom toolbar. I am using excel 2003.

1. I have created several macros in a workbook named "PLAN"

2. I then created a custom toolbar.

3. Through the customize dialog box, I assign the Macros to the custom tool bar button.

Everything work fine UNTIL...

If I do a SAVE AS for the workbook "PLAN" and call it something else like "PLAN2" and then close "PLAN2" and reopen "PLAN" the name of the macro assigned to the custom button has change

Where as before it was simply "PLAN"
now it is C:\Mydocuments\Test2.xls'!PLAN.
Since I have the workbook "PLAN" open and "PLAN2" is closed, excel now wants to open up "PLAN2" to access the macro assigned to that button.

I am creating a custom toolbar that will go with an excel workbook that I use as a template and will often resave it with different names.

I would like excel not to go looking in other workbooks for these macros since copies of the macros exist in the module attached to the workbook.

Every time I do a Save As the assigned macro gets renamed. I am not interested in making changes to Personal.xls, as other individuals will be using the workbook...

Does anyone have any ideas?

Maybe, something like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Activeworkbook.saveas............PLAN
End Sub

 

[Learner123]

Thanks for your responses!

The code that I am using is:

Option Explicit
Private Sub Auto_Open()
    'Make a commandbar when this workbook is opened
    CreateMyCommandBar
End Sub
Private Sub Auto_Close()
    
    'Delete a commandbar when this workbook is closed
    DelteMyCommandBar
End Sub
Private Sub CreateMyCommandBar()
    Dim ocb As CommandBarControl
    Dim objCommandBarButton As CommandBarButton
    'reset/delete a previous custom menu before create a new custom menu
    Call DelteMyCommandBar
    Set ocb = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
    ocb.Caption = "&My Button Name"
    'add a ControlButton to a commandbar
            
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Add Option A"
            .OnAction = "AddOptionA"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Add Option B"
            .OnAction = "AddOptionB"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Add Option C"
            .OnAction = "AddOptionC"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Open SQ"
            .OnAction = "OpenSQ"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Open Comp"
            .OnAction = "OpenComp"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Open ARF"
            .OnAction = "OpenARF"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Business Case - Light"
            .OnAction = "BusinessCase-Light"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Business Case - Full"
            .OnAction = "Business Case-Full"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Project Origin"
            .OnAction = "ProjectOrigin"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Feasibility Study"
            .OnAction = "FeasibilityStudy"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Final Account"
            .OnAction = "FinalAccount"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Menu"
            .OnAction = "Menu"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
    
    Set ocb = Nothing
    Set objCommandBarButton = Nothing
    
End Sub
Private Sub DelteMyCommandBar()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("&My Button Name").Delete
    Err.Clear: On Error GoTo -1: On Error GoTo 0
End Sub
Sub AddOptionA()
   Range("A4").Select
   Sheets("Option A").Visible = True
End Sub
Sub AddOptionB()
    Range("A4").Select
    Sheets("Option B").Visible = True
End Sub
Sub AddOptionC()
    Sheets("Option C").Visible = True
End Sub
Sub OpenSQ()
    Sheets("Status Quo").Visible = True
End Sub
Sub OpenComp()
    Sheets("Options Comparison").Visible = True
End Sub
Sub OpenARF()
   Sheets("Status Quo").Visible = True
   Sheets("ARF").Visible = True
   Sheets("ARF Input").Visible = True
End Sub
Sub OpenBCLite()
    Sheets("Business Case - Light").Visible = True
End Sub
Sub OpenBCFull()
    Sheets("Business Case - Full").Visible = True
End Sub
Sub OpenOrigin()
    Sheets("Project Origin").Visible = True
    
End Sub
Sub OpenFeasibility()
    Sheets("Feasibility Study").Visible = True
    
End Sub
Sub OpenFinalAccount()
    Range("A7").Select
    Sheets("Final Account").Visible = True
    Range("E50").Select
End Sub
Sub Menu()
    Sheets("Menu").Select
    Range("A31").Select
End Sub

However, it continues to reassign (link) the macros in the inital workbook to the newly created workbook.

Any ideas on how to correct this?