Code created Toolbar macros Save As causing problems

excel 2003

I have code that is creating a custom toolbar...
and deleting it at the end of the workbook entries.
and then re-creates it when the workbook is opened again..

problem:
When anyone does a "Save As"

I thought by creating the toolbar n code, i would eliminate the macros reading from the workbook/sheet the toolbar was made in...

i.e.
for a toolbar macro:
Non_Production_Enterprise_iFinal_Collection.xls!ShowPrebuilts   <---- I'm trying to get away from explicitly referencing a workbook and worksheet ???


Hope this make sense ?

Thanks
fordraiders

option explicit
Private cbr As CommandBar

Private Sub Workbook_Open()
Dim mysheet As Worksheet

 AddNewToolBar
end sub


Option Explicit
Sub AddNewToolBar()
     ' This procedure creates a new temporary toolbar.
    Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
    On Error GoTo ErrorHandler
     ' Create a new floating toolbar and make it visible.
    On Error Resume Next
     'Delete the toolbar if it already exists
    CommandBars("iFinal_Tools_Utilities_Enterprise").Delete
    Set ComBar = CommandBars.Add(Name:="iFinal_Tools_Utilities_Enterprise", Position:= _
    msoBarTop, Temporary:=True)
    ComBar.Visible = True
     ' Create a button with text on the bar and set some properties.
    Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
   ' prebuilts
    With ComBarContrl
        .FaceId = 611
        .Caption = "Prebuilts"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "ShowFormPrebuilts"
    End With
   Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
    ' extended search
    With ComBarContrl
        .FaceId = 1640
        .Caption = "Extended Search"
       .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "ShowFormExpSearch"
    End With
    Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
    ' combine locations
    With ComBarContrl
        .FaceId = 1647
        .Caption = "Combine Locations"
       .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "Sheet1.CreateNewLocationEZ"
    End With
    Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
    ' Utilities
    With ComBarContrl
        .FaceId = 611
        .Caption = "Utilities"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "ShowUtilities"
    End With
    Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
    ' create sort
    With ComBarContrl
        .FaceId = 1553
        .Caption = "Create Sort"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "Sheet1.FillNum"
    End With
    Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
    ' Mfgnames search
    With ComBarContrl
        .FaceId = 202
        .Caption = "Mfgnames Search"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "ShowForm"
    End With
    Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
    ' output report
    With ComBarContrl
        .FaceId = 176
        .Caption = "Output Report"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "CustomerReport"
    End With
        
 ' Duplicate Grainger Sku
     Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
     ' Create a button with an image on the bar and set some
     ' properties.
    
    With ComBarContrl
         'the facId line will let you choose an icon
         ' If you choose to use the faceId then the caption is not displayed
        .FaceId = 346
        .Caption = "Duplicate Grainger Sku"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro2"
         'the onaction line tells the button to run a certain macro
        .OnAction = "DupGraingerSku"
    End With
    
    
    'Duplicate Mfrnumber
     Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
     ' Create a button with an image on the bar and set some
     ' properties.
    
    With ComBarContrl
         'the facId line will let you choose an icon
         ' If you choose to use the faceId then the caption is not displayed
        .FaceId = 346
        .Caption = "Duplicate Mfrnumber"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro2"
         'the onaction line tells the button to run a certain macro
        .OnAction = "DupMfrnumSku"
    End With
    
    
    ' Duplicate Internal Partnumber
     Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
     ' Create a button with an image on the bar and set some
     ' properties.
    
    With ComBarContrl
         'the facId line will let you choose an icon
         ' If you choose to use the faceId then the caption is not displayed
        .FaceId = 346
        .Caption = "Duplicate Customer Number"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro2"
         'the onaction line tells the button to run a certain macro
        .OnAction = "DupInternalPartNumber"
    End With
    
    ' Remove Filter
     Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
     ' Create a button with an image on the bar and set some
     ' properties.
    
    With ComBarContrl
         'the facId line will let you choose an icon
         ' If you choose to use the faceId then the caption is not displayed
        .FaceId = 348
        .Caption = "Remove Filter"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro2"
         'the onaction line tells the button to run a certain macro
        .OnAction = "RemoveFilter"
    End With
    
    ' Reset iFinal
     Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
     ' Create a button with an image on the bar and set some
     ' properties.
    
    With ComBarContrl
         'the facId line will let you choose an icon
         ' If you choose to use the faceId then the caption is not displayed
        .FaceId = 478
        .Caption = "Reset iFinal"
        .Style = msoButtonIconAndCaption
        '.TooltipText = "Run Macro2"
         'the onaction line tells the button to run a certain macro
        .OnAction = "Sheet1.ResetProject"
    End With
  Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & vbCr & Err.Description
    Exit Sub
End Sub




End Sub

Select allOpen in new window