Workbook_Open command bar set up

[nigelandrewfoster]

The following code does what it should, but I'm for elegance, and it galls me to repeat code like this:

Private Sub workbook_open()
    Dim NewItem As CommandBarControl, OldItem As CommandBarControl
    
    On Error Resume Next
    Do
    Application.CommandBars("Cell").Controls("Product By Customer").Delete
    Loop Until Err
    Do
        Application.CommandBars("Cell").Controls("Outstanding Customers").Delete
    Loop Until Err
    Err.Clear
    Do
        Application.CommandBars("Cell").Controls("Direct Input").Delete
    Loop Until Err
    Err.Clear
    Do
        Application.CommandBars("Cell").Controls("Amend Despatch Info").Delete
    Loop Until Err
    Err.Clear
    Do
        Application.CommandBars("Cell").Controls("Print Individual Delivery Note").Delete
    Loop Until Err
    Err.Clear
    On Error GoTo 0

    Set NewItem = Application.CommandBars("Cell").Controls.Add
    NewItem.Caption = "Direct Input"
    NewItem.OnAction = "Direct_Input"
    NewItem.BeginGroup = True

    Set NewItem = Application.CommandBars("Cell").Controls.Add
    NewItem.Caption = "Amend Despatch Info"
    NewItem.OnAction = "Despatch_Info_Amend"
    NewItem.BeginGroup = True

    Set NewItem = Application.CommandBars("Cell").Controls.Add
    NewItem.Caption = "Print Individual Delivery Note"
    NewItem.OnAction = "Del_Note_Indiv"
    NewItem.BeginGroup = True
    
    Set NewItem = Application.CommandBars("Cell").Controls.Add
    NewItem.Caption = "Direct Input"
    NewItem.OnAction = "Direct_Input"
    NewItem.BeginGroup = True

    Set NewItem = Application.CommandBars("Cell").Controls.Add
    NewItem.Caption = "Amend Despatch Info"
    NewItem.OnAction = "Despatch_Info_Amend"
    NewItem.BeginGroup = True

    Set NewItem = Application.CommandBars("Cell").Controls.Add
    NewItem.Caption = "Print Individual Delivery Note"
    NewItem.OnAction = "Del_Note_Indiv"
    NewItem.BeginGroup = True
    'Call RightClickMenu
End Sub

However, what I thought was going to be a simple job of storing my Menu items on a worksheet and reading off the details into string variables to then pass to a general purpose routine like this:

Sub MenuItems(menu As String, item As String, replace, onaction As String, begin As Boolean)
    Dim NewItem As CommandBarControl
    Do
        On Error Resume Next
        [COLOR="Red"]Application.CommandBars(menu).Controls(item).Delete[/COLOR]
        If Err Then Exit Do
        On Error GoTo 0
    Loop

    If replace = 1 Then
        Set NewItem = Application.CommandBars(menu).Controls.Add
        NewItem.Caption = item
        NewItem.onaction = onaction
        NewItem.BeginGroup = begin
    End If
End Sub

It fails at the red line. The line works if I replace 'menu' and 'item' with "Cells" and "Direct Input", for example. Why doesn't it accept string variables as its arguments??
Thanks very much.
Nigel

 

[nigelandrewfoster]

.

 

[John_w]

To delete all the custom cell command bar buttons, use:

CommandBars("Cell").Reset

then you can get rid of your Delete loop, assuming you want to delete them all.

 

[nigelandrewfoster]

Thanks. I probably will use that method (which I had been warned against, in case I was using any third party add ons, whose menu items I would also be erasing b3 that method.
I still need to know why my code doesn't work when I am sending it valid values via string variagles...?