Macro keeps creating additional toolbars

[projectile]

Hi

I have this code that creates a toolbar each time excel opens, however it is creating multiple instances of the macro toolbar in excel , see screen shot.

The items in yellow are duplicates of the ones highlighted in red. Each time I start up Excel, I think it tries to create another instance of the toolbars.

Is there anyway to initially add the toolbars when the addin is installed, and for the code not to keep adding toolbars if they exist.

Hope this makes sense.

Sub SaveToolbarPos()
With CommandBars("ABC")
SaveSetting "MyUtils", "MyToolbar", "Pos", .Position
SaveSetting "MyUtils", "MyToolbar", "Top", .Top
SaveSetting "MyUtils", "MyToolbar", "Left", .Left
SaveSetting "MyUtils", "MyToolbar", "RowIndex", .RowIndex
End With
'DeleteSetting "MyUtils", "MyToolbar"
End Sub

Sub SetToolbarPos()
With CommandBars("IDModeling")
.Position = GetSetting("MyUtils", "MyToolbar", "Pos", msoBarFloating)
.Top = GetSetting("MyUtils", "MyToolbar", "Top", 1)
.Left = GetSetting("MyUtils", "MyToolbar", "Left", 1)
.RowIndex = GetSetting("MyUtils", "MyToolbar", "RowIndex", 1)
End With
End Sub

Option Explicit

Public Const ToolBarName As String = "House Bill"

'===========================================
Sub Auto_Open()
    Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
    Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
    On Error Resume Next
    Application.CommandBars(ToolBarName).Delete
    On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()

    Dim iCtr As Long

    Dim MacNames As Variant
    Dim CapNamess As Variant
    Dim TipText As Variant

    Call RemoveMenubar

    MacNames = Array("HouseBill")

    CapNamess = Array("Create EDI House Bill File")

    TipText = Array("HouseBill tip")

    With Application.CommandBars.Add
     '   .Name = ToolBarName
      '  .Left = 200
       ' .Top = 50
      '  .Protection = msoBarNoProtection
      '  .Visible = True
      '  .Position = msoBarFloating

        For iCtr = LBound(MacNames) To UBound(MacNames)
            With .Controls.Add(Type:=msoControlButton)
                .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
                .Caption = CapNamess(iCtr)
                .Style = msoButtonIconAndCaption
                .FaceId = 71 + iCtr
                .TooltipText = TipText(iCtr)
            End With
        Next iCtr
    End With
End Sub

 

[AgentSmith]

A couple of thoughts:

1) Depending on the version of Excel, I'd suggest you use Workbook events rather than Autpen and Auto_Close. E.g. Workbook_Open or Workbook_AddInInstall instead of Autpen.

2) Amend the delete macro to cater for multiple instances, e.g.

Sub RemoveMenubar()
    Dim bar As CommandBar
    
    For Each bar In Application.CommandBars
        If bar.Name = ToolBarName Then bar.delete
    Next bar
End Sub

3) With (2) in situ, insert a line at the top of CreateMenubar thats calls RemoveMenubar first. Then multiple instances will be detected (and removed) whenever you attempt to create the bar too.

 

[projectile]

Hi

I think I done as you suggested, however it is still creating multiple instances;

Any ideas where I have gone wrong?

 

[AgentSmith]

I'm surprised you're still getting multiple instances.

Another idea - and a bit of a stab in the dark if I'm honest! Toolbar problems suggests there may be problems with a file called excel11.xlb (the numbers change with the version of Excel). If you don't mind resetting all the built-in toolbars to default settings then you can simply delete this file, but you must quit Excel before doing so. On my computer it's saved in C:\Documents and Settings\(Username)\Application Data\Microsoft\Excel but that could be different on yours.

NB: If Excel is open when you delete this file then it will come back automatically!