Results 1 to 5 of 5

Thread: Okay, I'm stumped - On Menu creation
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2005
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Okay, I'm stumped - On Menu creation

    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.
    Code:
    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...

  2. #2
    Board Regular KenCriss's Avatar
    Join Date
    Jun 2005
    Location
    Winston-Salem, NC
    Posts
    323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,928
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Code:
        MenuName = "&MyMenu"
        On Error Resume Next
        Do While Err = 0
            CommandBars("Worksheet Menu Bar").Controls(MenuName).Delete
        Loop
        On Error GoTo 0
    “The early bird gets the worm, but the second mouse gets the cheese.”

  4. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi [Select a UserName]

    The simplest approach is to enclose your deletion line between:


    On Error Resume Next

    and

    On Error GoTo 0
    Richie

  5. #5
    Board Regular KenCriss's Avatar
    Join Date
    Jun 2005
    Location
    Winston-Salem, NC
    Posts
    323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •