Drop Down Menu

[lhernandez]

Can someone help by telling me how to make a drop down menu (like File Edit View... at the top of a window)?
I would like to make a drop down menu and have the macros attached to it.
Anything ideas would help
Thank you

 

[lhernandez]

I was able to fix the previous problem thank you for all the help.
Any advice on how to make a sub menu?

 

[Joe Was]

OK, I modified my original code to also include a sub-menu to the MyMenu toolbar popup, it is called "Tools" and has two tool items added to it. The code for this starts at Item 6 in the code below. Notice the change in the MSO object name and the reduced code for getting the sub-menu.


Sub myAdd_MyMenu_ToDefaultToolbar()
'Standard module code, like: Module1!
Dim myNewMainMenu, myNewMainMenuItem
'This adds a new item to the Default toolbar at the very top of the page, like: File Edit View...

On Error GoTo myErr
'Delete custom menu if it exists!
Call Remove_MyMenu

'Add a new item to the default toolbar!
With CommandBars("Worksheet Menu Bar")
Set myNewMainMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True)
End With

'Name this new toolbar item!
myNewMainMenu.Caption = "MyMenu"

'Add a sub-menu item to the new menu list!
Set myNewItem1 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem1
.Caption = "Un-Install"
.TooltipText = "Un-install this MyMenu from this toolbar!"
.Style = msoButtonCaption
.OnAction = "Remove_MyMenu" 'Name of macro to run.
End With

'Add a Second sub-menu item to the new menu list!
Set myNewItem2 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem2
.Caption = "Run Test1"
.TooltipText = "Runs the macro for this item!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With

'Add a Third sub-menu item to the new menu list!
Set myNewItem3 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem3
.Caption = "Run Test2"
.TooltipText = "Runs the macro for this item!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With

'Add a Fourth sub-menu item to the new menu list!
Set myNewItem4 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem4
.Caption = "Run Test3"
.TooltipText = "Runs the macro for this item!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With

'Add a Fith sub-menu item to the new menu list!
Set myNewItem5 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem5
.Caption = "Run Test4"
.TooltipText = "Runs the macro for this item!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With

'Add a SubMenu catagory item to the new menu list!
Set myNewSubMen6 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlPopup, temporary:=True)

With myNewSubMen6
.Caption = "Tools"
.TooltipText = "Sub-Menu of Options!"
End With

'Add an Item1 to the "Tools" sub-menu!
Set myNewSubItem1 = myNewSubMen6.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewSubItem1
.Caption = "Tool1"
.TooltipText = "Runs the macro for this tool!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With

'Add an Item2 to the "Tools" sub-menu!
Set myNewSubItem2 = myNewSubMen6.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewSubItem2
.Caption = "Tool2"
.TooltipText = "Runs the macro for this tool!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With
Exit Sub

myErr:
MsgBox "An error has occured, did not create menu items. " & Chr(13) & _
"Error number: " & Err.Number & Chr(13) & "Error Description: " & Err.Description, vbExclamation + vbOKOnly, "Error!"
Resume Next
End Sub