Adds button, combobox, text box and drop down to tools menu : Menu « Application « VBA / Excel / Access / Word

Adds button, combobox, text box and drop down to tools menu

Sub ExpandMenu()
  Dim cbc As CommandBarControl
  Dim cbb As CommandBarButton
  Dim cbcm As CommandBarComboBox
  Dim cbp As CommandBarPopup
  With Application.CommandBars("Worksheet Menu Bar").Controls("tools")
    Set cbc = .Controls.Add(Id:=3, Temporary:=True)
    Set cbb = .Controls.Add(Temporary:=True)
    cbb.Caption = "A new command"
    cbb.Style = msoButtonCaption
    cbb.OnAction = "NewCommand_OnAction"
    Set cbcm = .Controls.Add(Type:=msoControlComboBox, Temporary:=True)
    cbcm.Caption = "Combo:"
    cbcm.AddItem "list entry 1"
    cbcm.AddItem "list entry 2"
    cbcm.OnAction = "NewCommand_OnAction"
    cbcm.Style = msoComboLabel
    Set cbc = .Controls.Add(Type:=msoControlEdit, Temporary:=True)
    cbc.Caption = "Text box:"
    cbc.Text = "Type in a text:"
    cbc.OnAction = "MenuText_OnAction"
    Set cbc = .Controls.Add(Type:=msoControlDropdown, Temporary:=True)
    cbc.Caption = "Dropdown:"
    cbc.AddItem "list entry 1"
    cbc.AddItem "list entry 2"
    cbc.OnAction = "MenuDropdown_OnAction"

    Set cbp = .Controls.Add(Type:=msoControlPopup, Temporary:=True)
    cbp.Caption = "new sub menu"
    Set cbb = cbp.Controls.Add
    cbb.Caption = "sub entry 1"
    Set cbb = cbp.Controls.Add
    cbb.Caption = "sub entry 2"
  End With
End Sub

Sub MenuCombo_OnAction()
  MsgBox "Selected list entry: " & _
    CommandBars("Worksheet Menu Bar").Controls("tools").Controls("Combo:").Text
End Sub
Sub MenuText_OnAction()
  MsgBox "New text: " & _
    CommandBars("Worksheet Menu Bar").Controls("tools").Controls("Text box:").Text
End Sub
Sub MenuDropdown_OnAction()
  MsgBox "Selected list entry: " & _
    CommandBars("Worksheet Menu Bar").Controls("tools").Controls("Dropdown:").Text
End Sub


Related examples in the same category

1.Creating a Menu Bar
2.Add a new menubar with toggle action
3.Controling a CommandBarControl's Visibility
4.Deletes a control tagged "MyMenu2"
5.Create Submenus
6.List Menu Information
7.List ShortCut Menus
8.Adding and deleting menus automatically
9.Disabling or hiding menus
10.Working with checked menu items
11.Disabling a shortcut menu
12.Enables the shortcut menu when the workbook is closed.
13.Create a new menu bar that replaces the existing menu bar
14.Adding a menu: Take 1
15.Adding the Budgeting menu to Excel's main menu bar
16.Determined the Id property of the Help menu by executing the following statement:
17.Deleting a menu from a menu bar
18.Adding selections and submenu items to the Budgeting menu
19.Adding a selection to Excel's Tools menu
20.Deleting a menu item from the Tools menu
21.Adding a menu selection that features a shortcut key
22.Adding menu items to shortcut menus
23.Deleting menu items from shortcut menus
24.removes the Hide menu item from two shortcut menus: the one that appears when you right-click a row header and the one that appears for a column header.
25.Disabling shortcut menu items
26.Disabling shortcut menus
27.If you want to disable all shortcut menus, use the following procedure:
28.Resetting shortcut menus: The Reset method restores a shortcut menu to its original condition.
29.Creating an entirely new and separate shortcut menu
30.Adding a new item to the Cell shortcut menu
31.Create a popup menu