Okay, I'm stumped - On Menu creation

[crisskw]

I am calling this module from the Workbook_Open event. I get the compile error "A module is not a valid type" on "Dim cBarParentBar As CommandBar". I have tried this before with success, although it was in a different workbook.

Sub SetUpCommandBar()
    Dim cBarParentBar As CommandBar
    Dim cBarParentBarPopUp As CommandBarPopup
    Dim cBarParentBarPopUpControls(1 To 5) As CommandBarButton
    Dim intArrayCounter As Integer
    
    Set cBarParentBar = Application.CommandBars("Worksheet Menu Bar")

    On Error Resume Next
    cBarParentBar.Controls("Excel to CSV").Delete
    On Error GoTo 0

    Set cBarParentBarPopUp = cBarParentBar.Controls.Add( _
        msoControlPopup, , , , True)

    With cBarParentBarPopUp
        .Caption = "Excel to CSV"
        .Visible = True
    End With

    For intArrayCounter = 1 To 5
        Set cBarParentBarPopUpControls(intArrayCounter) = cBarParentBarPopUp.Controls.Add( _
            msoControlButton, , , , True)

        With cBarParentBarPopUpControls(intArrayCounter)
            .Caption = "Add button # " & intArrayCounter
            .Style = msoButtonCaption
            .Visible = True
            .OnAction = "CommandBarButton_Click"
            .Tag = intArrayCounter
        End With
    Next

End Sub

I would appreciate any suggestions. I have looked at this long enough...

 

[KenCriss]

Okay, now that I have had some sleep, I can see my error, I inadvertantly named another module CommandBar to some silly reason. I changed that name and now it runs. However, another question. I know how to delete my custom menu on WorkBook_BeforeClose, but is there a good way to check and see if the menu is present before deleting it?

 

[btadams]

try this (just in case there are multiple menus with the same name):

    MenuName = "&MyMenu"
    On Error Resume Next
    Do While Err = 0
        CommandBars("Worksheet Menu Bar").Controls(MenuName).Delete
    Loop
    On Error GoTo 0

 

[Richie(UK)]

Hi [Select a UserName]

The simplest approach is to enclose your deletion line between:


On Error Resume Next

and

On Error GoTo 0

 

[KenCriss]

Thanks. That's the approach I was taking, but wasn't sure that it was the best approach.