Custom menu
Hi Bob
Thank you very much for your support. You are a star! Everything works fine.
Simon
"Bob Phillips" wrote:
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,
_
Befo=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)
"Simon Minder" wrote in message
...
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:=ms oControlPopup,
temporary:=True)
cbcCutomMenu.Caption = "Return Analysis"
instead of
Set cbcCutomMenu =
cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Return Analysis"
?
Thanks.
Simon Minder
"Bob Phillips" wrote:
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)
"Simon Minder" wrote in message
...
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
|