Hi all,

I have a problem with my custom menu. I would like to have several levels of
the menu with sub menus. Unfortunately, I don't get back to the top-level.

Example:
1
1.1
1.2
2
3

Instead of:
1
1.1
1.2
2
3

I guess I need to include some code between:

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "German"
.FaceId = 420
.OnAction = "TheFinancialServiceProvidersChartG"
End With

' and this set of code

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Return Analysis"

to come back to the first level. Otherwise I can only add menu links on the
same level.

Please find below the whole code. I would like to have "Return Analysis" on
the same level as the "The Financial Service Providers".

Can anybody help?

Kind regards,

Simon Minder




VBAProject
----------------------------------------------
Microsoft Excel Objekcts - ThisWorkbook
----------------------------------------------
Option Explicit

Private Sub Workbook_Activate()
Run "AddMenus"
End Sub

Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub

-----------------------------------
Modules - CommandBarMacro
-----------------------------------
Option Explicit

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
Dim I As Integer
Dim sBar As String


'(1)Delete any existing one. We must use On Error Resume next in case it
does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MFO").Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
For I = 1 To 2
sBar = IIf(I = 1, "Worksheet Menu Bar", "Chart Menu Bar")
Set cbMainMenuBar = Application.CommandBars(sBar)

'(3)Return the Index number of the Help menu. We can then use this
to place a custom menu before.
iHelpMenu = cbMainMenuBar.Controls("Help").Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help. Set a
CommandBarControl variable to it
Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Befo=iHelpMenu)

'5)Give the control a caption
cbcCutomMenu.Caption = "MFO"

'Add another menu that will lead off to another menu. Set a
CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)

'Give the control a caption
cbcCutomMenu.Caption = "The Financial Service Providers"

'Add a contol to the sub menu, just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Data"
.FaceId = 420
.OnAction = "TheFinancialServiceProvidersData"
End With

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Chart"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "English"
.FaceId = 420
.OnAction = "TheFinancialServiceProvidersChartE"
End With

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "German"
.FaceId = 420
.OnAction = "TheFinancialServiceProvidersChartG"
End With

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Return Analysis"



Next

End Sub

Sub DeleteMenu()

On Error Resume Next

Application.CommandBars("Worksheet Menu Bar").Controls("MFO").Delete
Application.CommandBars("Chart Menu Bar").Controls("MFO").Delete

On Error GoTo 0

End Sub

-------------------------------
Modules - OnActionMacros
-------------------------------
Option Explicit
Sub TheFinancialServiceProvidersData()
Sheets("The Financial Service Providers").Select
End Sub
Sub TheFinancialServiceProvidersChartE()
Sheets("The Financial Ser. Pro. Graph E").Select
End Sub
Sub TheFinancialServiceProvidersChartG()
Sheets("The Financial Ser. Pro. Graph G").Select
End Sub