Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom menu
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom menu | Excel Discussion (Misc queries) | |||
VBA - Disappearing custom menu and custom toolbar | Excel Programming | |||
Custom Menu return to Excel Menu upon Closing | Excel Programming | |||
Custom Menu | Excel Programming | |||
Custom Menu | Excel Programming |