Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding a group of menu items to other menu bars

Excel How To

Adding a group of menu items to other menu bars

by  shetlandbob  Posted    (Edited  )
This is an add on to an existing [link http://www.tek-tips.com/faqs.cfm?fid=5255]FAQ[/link] which explains how to add in your own menu.

Have you ever wanted to add a menu item to say the tools menu? Or even better still your own group under the tools menu? (this will work for any other menu as well, I've just used the tools as an example)

Well here's how:

Code: 
Sub addMenu()
  Dim cmdbar As CommandBar
  Dim toolsMenu As CommandBarControl
  Dim myMenu As CommandBarPopup
  Dim subMenu As CommandBarControl
  
[green]' Point to the Worksheet Menu Bar[/green]
  Set cmdbar = Application.CommandBars("Worksheet Menu Bar")

[green]' Point to the Tools menu on the menu bar[/green]
  Set toolsMenu = cmdbar.Controls("Tools")

[green]' Create My Menu[/green]
  Set myMenu = toolsMenu.Controls.Add(Type:=msoControlPopup)
  
[green]' Create the sub Menu(s)[/green]
  Set subMenu = myMenu.Controls.Add
  
  With myMenu
    .Caption = "My Menu"
    .BeginGroup = True
    With subMenu
      .Caption = "sub Menu"
      .BeginGroup = True
      .OnAction = "'" & ThisWorkbook.name & "'!myMacro" [green]' Assign Macro to Menu Item[/green]
    End With
  End With

End Sub
Private Sub myMacro()
  MsgBox ("My Sub Menu Command")
End Sub

[green] ' How to remove the menu item [/green]
Sub removeMenu()

  On Error Resume Next
  Dim cmdbar As CommandBar
  Dim CmdBarMenu As CommandBarControl
  Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
  Set CmdBarMenu = cmdbar.Controls("Tools")
  CmdBarMenu.Controls("My Menu").Delete
End Sub


Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top