Drop Down Menu

Just did this for someone else, here is the link:


http://www.mrexcel.com/board2/viewtopic.php?t=226256

Tried it out...
it works..
thank you!

How can I add sub menus? Ex: if you click on
>>file
>>new
and an additional box opens with more options. So I assume NEW it a title option with associated macros attached to it?

I have used the code you suggested and everything works fine, until I try to add more options. I have a total of five and it only shows two. I changed
Set myNewItem1 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

and

With myNewItem1

each time as the number of options incremented. Is there something I missed?

Also when I open and close the template I am working with the MenuItem does not automatically show up. How can I make it to when the workbook opens have the toolbar display?

This uses my original code and now adds 5 menu items, so you can see how to do it. I tested it and it works for me!

This code must go into a Standard code module like: Module1!


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
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


Sub Remove_MyMenu()
'Standard module code, like: Module1!
'Removes custom menu if it exists!

On Error Resume Next

CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0
End Sub

Private Sub myTestItem()
'Standard module code, like: Module1!

MsgBox "This is a test for a Sub-Menu item activation!"
End Sub


This next code can only go into the "ThisWorkbook" code module!


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ThisWorkbook module code, Only!
'Removes custom menu if it exists!
On Error Resume Next

CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
'ThisWorkbook module code, Only!
'Adds custom menu to this workbook.

Call myAdd_MyMenu_ToDefaultToolbar
End Sub

Private Sub Workbook_Deactivate()
'ThisWorkbook module code, Only!
'Removes custom menu if it exists!

Call Remove_MyMenu
End Sub

Private Sub Workbook_Activate()
'ThisWorkbook module code, Only!
'Adds custom menu to this workbook.

Call myAdd_MyMenu_ToDefaultToolbar
End Sub



If you paste the code correctly it should be automatic.

To get it to work save it close it and re-open it and all the code will be set from then on it will work automatically!

I am sorry, this is prob. a tedious question but what is the differnce between module1! and a worksheet module?

Code can be written to six places, five are in the VBE Visual Basic Editor the sixth is the Microsoft Script Editor.

There are two types of Modules, Standard modules and Class modules all are part of the VBA collection known as the Project.

The Standard modules [Module1...] are: A module containing only procedure, type, and data declarations and definitions. Module-level declarations and definitions in a standard module are Public by default. A standard module is referred to as a code module in earlier versions of Visual Basic. This is where recorded steps become a Macro. General code is written here. As code contained here is not at the sheet level, it is at the Workbook level, here each reference to a Sheet must be explicit. These are the third modules checked by Excel.

All other modules are the Class module or sub sets of the Classs module. These include: the Sheet modules, the ThisWorkbook module and the UserForms module.

The general Class module is where code that extends the functionality of VBA is stored. A class module defines an object, its properties, and its methods. A class module acts as a template from which an instance of an object is created at run time. VBA project contains a class module called ThisWorkbook; this class module represents the properties, methods, and events of the Microsoft® Excel®. You define and build your classes of objects here. These modules are the first or second modules checked by Excel, if you count the ThisWorkbook parent also called ThisWorkbook [where all of the core Excel is held] not to be confused with the "ThisWorkbook" code module indicated below.

All code in a Sheet module [Sheet1...] is implicit to that sheets module and no reference is needed to that sheet, unless you work on a different sheet than that modules and then return back to it. This module is for code that acts directly upon and for that sheet. All sheet level Events are in these modules. Indirectly at the Workbook level, in the "ThisWorkbook" module, you can access sheet change related events as well. This is the fourth module checked by Excel, with the WorkSheet with the current Scope being the first of these modules checked at this time.

The ThisWorkbook module is checked before any other module and is the module for the primary Workbook in scope code and its Events. This is where code that effects the whole Workbook is stored. It has some things in common with the general Class module and the Sheet modules only it's basic scope is for the workbook as a whole, unlike the Sheet module. The workbook reference is implicit to this module.

The Form modules are for the code that directly effects the named UserForm. It is checked last unless the current scope is for that UserForm.

Thank you for the explanation.
I can get the menu Item to work if I manually run the macro, but if i save the workbook, close and then re-open i get an eeror in the "ThisWorkbook Code Module"
I have changed all the "MyMenu" to "EIEP"

Private Sub Workbook_Activate()
'ThisWorkbook module code, Only!
'Adds custom menu to this workbook.

Call myAdd_EIEP_ToDefaultToolbar
End Sub

I get an error here saying ambigious name deteced.
and this is highlighted:
Call myAdd_EIEP_ToDefaultToolbar

am I not to change the name?