Custom toolbar is not available to other users

[deepfriedcheese]

I have a custom toolbar in a spreadsheet that is used by many different people. The toolbar has a single button to trigger a macro that inserts unlocked lines in a protected workbook.

The custom toolbar shows up when the file is opened on my machine (Win XP, Excel 2003), but it will not show up when anyone else opens the file on their machine (also Win XP, Excel 2003).

I originally created the toolbar, attached it to the workbook, assigned the macro, and created a workbook_open macro to enable the toolbar. Then I added a workbook_close macro to disable the toolbar so it wouldn't persist into other spreadsheets the user might open after the original was closed. The problem I ran into here was that I could not get the button to retain the macro assignment. Every time I reopened the spreadsheet, the toolbar appeared just like it should, had the button I had created, and had no macro assigned to it.

I quickly tired of fighting that battle, so I went to Google and found some code that would create the toolbar on workbook_open and delete it on workbook_close. Sure enough it works like magic on my machine, but when I copy the file to anyone else, they do not get a toolbar. The code I'm using to do this is below.

I'd appreciate any help in getting this toolbar to work on other machines.

Private Sub Auto_Open()
    'Make a commandbar when this workbook is opened
    CreateMyCommandBar
End Sub
 
Sub RS620InsertRow()
Msgbox "This code would have run if the button had been available."
End Sub
 
Sub Auto_Close()
 
    'Delete a commandbar when this workbook is closed
    DeleteMyCommandBar
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 DeleteMyCommandBar
    Set ocb = Application.CommandBars("RS620 Tool Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
    ocb.Caption = "&RS620 Insert Row"
    'add a ControlButton to a commandbar
 
    Set objCommandBarButton = ocb.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Insert Row Macro"
            .OnAction = "RS620InsertRow"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
 
    Set ocb = Nothing
    Set objCommandBarButton = Nothing
 
End Sub
 
Private Sub DeleteMyCommandBar()
    On Error Resume Next
    Application.CommandBars("RS620 Tool Bar").Controls("&RS620 Insert Row").Delete
    Err.Clear: On Error GoTo -1: On Error GoTo 0
End Sub

 

[Andrew Poulsom]

Do the other users have macros enabled?

 

[RoryA]

Your code assumes they have a toolbar named "RS620 Tool Bar" - is that going to be the case?

 

[deepfriedcheese]

The other users do have macros enabled.

The other users would not have the RS620 Tool Bar. I want it to exist only within this workbook. I see the logic flaw that you have pointed out. How do I fix it?

 

[RoryA]

You need to create the commandbar too:

Private Sub CreateMyCommandBar()
    Dim ocb As CommandBarControl
   Dim cbr as CommandBar
    Dim objCommandBarButton As CommandBarButton
    'reset/delete a previous custom menu before create a new custom menu
    Call DeleteMyCommandBar
   Set cbr = Application.CommandBars.Add("RS620 Tool Bar")
    Set ocb = cbr.Controls.Add(Type:=msoControlPopup, Temporary:=True)
    ocb.Caption = "&RS620 Insert Row"
    'add a ControlButton to a commandbar
 
    Set objCommandBarButton = ocb.Controls.Add(Type:=msoControlButton, ID:=1)
    With objCommandBarButton
            .Caption = "Insert Row Macro"
            .OnAction = "RS620InsertRow"
            .Style = msoButtonIconAndCaption
            .FaceId = 2892
            .BeginGroup = False
    End With
 
    Set ocb = Nothing
    Set objCommandBarButton = Nothing
 
End Sub

then your delete code can simply delete that toolbar rather than any controls.

 

[deepfriedcheese]

That worked nicely. I shortened the auto_close down to just delete the toolbar as you suggested and I added a line to make the toolbar default to visible when the workbook is opened.

Thank you very much for the help.