Custom menu

[Guest]

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,
Before:=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

 

[Bob Phillips]

Simon,

Here is an example

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls("Tools").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "1"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "1.1"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "macro11"
End With
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "1.2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "macro12"
End With
Set oCtl = .Controls("Tools").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "2"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "2.1"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "macro11"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob

Thank you very much for your quick response and your example. However, I
have a bit a problem to apply your example to my code.

Do I only need to change the code like that:

Set cbcCutomMenu =
cbcCutomMenu.Controls("MFO").Controls.Add(Type:=msoControlPopup,
temporary:=True)
cbcCutomMenu.Caption = "Return Analysis"

instead of

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

?

Thanks.

Simon Minder

 

[Bob Phillips]

Simon,

I think it is a it more fundamental than that.

I have recut it to show how I wouold do it

'-----------------------------------
'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,
_
Before:=iHelpMenu)

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

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

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

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

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

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

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

With .Controls.Add(Type:=msoControlPopup)
.Caption = "Return Analysis"
End With

End With
End With
Next

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob

Thank you very much for your support. You are a star! Everything works fine.

Simon