Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: create drop-down menu with macros

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to create a drop-down menu in excel with macros. Is there a site with sample codes and/or instructions? How are the codes and separate worksheets work together? Thanks!

  2. #2
    New Member
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will add a new "Budgeting menu to the worksheet menu bar and will be positioned just to the left of the Help menu:

    Sub CreateMenu()
    Dim HelpMenu As CommandBarControl
    Dim NewMenu As CommandBarPopup
    Dim MenuItem As CommandBarControl
    Dim Submenuitem As CommandBarButton

    ' Delete the menu if it already exists
    Call DeleteMenu

    ' Find the Help Menu
    Set HelpMenu = CommandBars(1).FindControl(Id:=30010)

    If HelpMenu Is Nothing Then
    ' Add the menu to the end
    Set NewMenu = CommandBars(1).Controls.Add _
    (Type:=msoControlPopup, _
    temporary:=True)
    Else
    ' Add the menu before Help
    Set NewMenu = CommandBars(1).Controls.Add _
    (Type:=msoControlPopup, _
    Before:=HelpMenu.Index, _
    temporary:=True)
    End If

    ' Add a caption for the menu
    NewMenu.Caption = "&Budgeting"

    ' FIRST MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
    (Type:=msoControlButton)
    With MenuItem
    .Caption = "&Data Entry..."
    .FaceId = 162
    .OnAction = "Macro1"
    End With

    ' SECOND MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
    (Type:=msoControlButton)
    With MenuItem
    .Caption = "&Generate Reports..."
    .FaceId = 590
    .OnAction = "Macro2"
    End With

    ' THIRD MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
    (Type:=msoControlPopup)
    With MenuItem
    .Caption = "View &Charts"
    .BeginGroup = True
    End With

    ' FIRST SUBMENU ITEM
    Set Submenuitem = MenuItem.Controls.Add _
    (Type:=msoControlButton)
    With Submenuitem
    .Caption = "Monthly &Variance"
    .FaceId = 420
    .OnAction = "Macro3"
    End With

    ' SECOND SUBMENU ITEM
    Set Submenuitem = MenuItem.Controls.Add _
    (Type:=msoControlButton)
    With Submenuitem
    .Caption = "Year-To-Date &Summary"
    .FaceId = 422
    .OnAction = "Macro4"
    End With

    End Sub

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
  •