Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sub menu in Command bar
Hi All,
it is possible to add an image (icon) to the msoControlPopup, like it is the case for the msoControlButton ?For answers thanks in advance. Cheers, Florian "Greg Wilson" wrote: For the sake of brevity, I only show how to: 1) Create a new toolbar 2) Add a popup type control (that supports menu items) to the toolbar 2) Add menu items to the popup control (menu control) 3) Add a popup control (that supports submenu items) to the first popup control 4) Add submenu items to the second popup control 5) Use arrays and loops to drastically cut down on the code requirement. You can continue the same branching process indefinately to my knowledge. Regards, Greg Sub MakeNewMenu() Dim CB As CommandBar Dim NewMenu As CommandBarControl Dim MenuItm As CommandBarControl Dim SubMenuItm As CommandBarControl Dim Arr1 As Variant, Arr2 As Variant Dim Arr3 As Variant Dim i As Integer With Application .ScreenUpdating = False 'Menu item caption list Arr1 = Array("Caption 1", "Caption 2", _ "Caption 3", "Caption 4", "Caption 5") 'Menu item macro list Arr2 = Array("Menu macro 1", "Menu macro 2", _ "Menu macro 3", "Menu macro 4", "Menu macro 5") 'Menu item FaceId list Arr3 = Array(100, 101, 102, 103, 104) Set CB = .CommandBars.Add("Data Analysis", Temporary:=True) Set NewMenu = CB.Controls.Add(msoControlPopup) NewMenu.Caption = "Menu items" 'ToolTipText supported on this level only NewMenu.TooltipText = "Select from my macros" 'When referencing elements in an array, the first 'element has an index value of zero (hence 0 to 4) For i = 0 To 4 Set MenuItm = NewMenu.Controls.Add With MenuItm .Caption = Arr1(i) .Style = msoButtonIconAndCaption .OnAction = Arr2(i) .FaceId = Arr3(i) End With Next 'Submenu item caption list Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _ "Caption 9", "Caption 10") 'Submenu item macro list Arr2 = Array("Submenu macro 1", "Submenu macro 2", _ "Submenu macro 3", "Submenu macro 4", "Submenu macro 5") 'Submenu item FaceId list Arr3 = Array(200, 201, 202, 203, 204) 'Now add popup type control to support the submenus Set MenuItm = NewMenu.Controls.Add(msoControlPopup) MenuItm.Caption = "Sub menu items" For i = 0 To 4 Set SubMenuItm = MenuItm.Controls.Add With SubMenuItm .Caption = Arr1(i) .Style = msoButtonIconAndCaption .OnAction = Arr2(i) .FaceId = Arr3(i) End With Next CB.Visible = True DoEvents .ScreenUpdating = True End With End Sub "Anders" wrote: Hi all, I would like to create a command bar that contains a menu with sub menues. Do anyone know how to do this? Menu ------- Menu Item1 Menu Item2 - Sub menu item - Sub sub menu item Regards Anders |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sub menu in Command bar
The answer is no. However, instead of using a msoControlPopup, you can create
a popup type toolbar and call it using a regular msoControlButton (which supports a FaceId). The affect is about the same. Note that this method is sometimes advantagious because the macro can be used to change the status of controls on the popup toolbar (e.g. enabled property etc.) before showing the toolbar; whereas the click event of the msoControlPopup cannot be accessed before the dropdown (or popup toolbar ?) is displayed. You would have to use other, less convenient, events to initiate change of the control properties. Regards, Greg "Troispieds" wrote: Hi All, it is possible to add an image (icon) to the msoControlPopup, like it is the case for the msoControlButton ?For answers thanks in advance. Cheers, Florian "Greg Wilson" wrote: For the sake of brevity, I only show how to: 1) Create a new toolbar 2) Add a popup type control (that supports menu items) to the toolbar 2) Add menu items to the popup control (menu control) 3) Add a popup control (that supports submenu items) to the first popup control 4) Add submenu items to the second popup control 5) Use arrays and loops to drastically cut down on the code requirement. You can continue the same branching process indefinately to my knowledge. Regards, Greg Sub MakeNewMenu() Dim CB As CommandBar Dim NewMenu As CommandBarControl Dim MenuItm As CommandBarControl Dim SubMenuItm As CommandBarControl Dim Arr1 As Variant, Arr2 As Variant Dim Arr3 As Variant Dim i As Integer With Application .ScreenUpdating = False 'Menu item caption list Arr1 = Array("Caption 1", "Caption 2", _ "Caption 3", "Caption 4", "Caption 5") 'Menu item macro list Arr2 = Array("Menu macro 1", "Menu macro 2", _ "Menu macro 3", "Menu macro 4", "Menu macro 5") 'Menu item FaceId list Arr3 = Array(100, 101, 102, 103, 104) Set CB = .CommandBars.Add("Data Analysis", Temporary:=True) Set NewMenu = CB.Controls.Add(msoControlPopup) NewMenu.Caption = "Menu items" 'ToolTipText supported on this level only NewMenu.TooltipText = "Select from my macros" 'When referencing elements in an array, the first 'element has an index value of zero (hence 0 to 4) For i = 0 To 4 Set MenuItm = NewMenu.Controls.Add With MenuItm .Caption = Arr1(i) .Style = msoButtonIconAndCaption .OnAction = Arr2(i) .FaceId = Arr3(i) End With Next 'Submenu item caption list Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _ "Caption 9", "Caption 10") 'Submenu item macro list Arr2 = Array("Submenu macro 1", "Submenu macro 2", _ "Submenu macro 3", "Submenu macro 4", "Submenu macro 5") 'Submenu item FaceId list Arr3 = Array(200, 201, 202, 203, 204) 'Now add popup type control to support the submenus Set MenuItm = NewMenu.Controls.Add(msoControlPopup) MenuItm.Caption = "Sub menu items" For i = 0 To 4 Set SubMenuItm = MenuItm.Controls.Add With SubMenuItm .Caption = Arr1(i) .Style = msoButtonIconAndCaption .OnAction = Arr2(i) .FaceId = Arr3(i) End With Next CB.Visible = True DoEvents .ScreenUpdating = True End With End Sub "Anders" wrote: Hi all, I would like to create a command bar that contains a menu with sub menues. Do anyone know how to do this? Menu ------- Menu Item1 Menu Item2 - Sub menu item - Sub sub menu item Regards Anders |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sub menu in Command bar
Thanks for your quick response. At the moment I don't know how I can manage
your suggestion. Therefore I added my source coed below. Perhaps you might help me again. Again thanks in advance. Cheers, Florian Sub add_toolbar() Dim Cbar As CommandBar Dim cbpop As CommandBarControl Dim cbctl As CommandBarControl Dim cbsub As CommandBarControl Set Cbar = CommandBars("myToolbar") Cbar.Visible = True Set cbpop = Cbar.Controls.Add(Type:=msoControlPopup, Temporary:=True) cbpop.Caption = "my_first_Popup" cbpop.Visible = True Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup) cbsub.Visible = True cbsub.BeginGroup = True cbsub.Caption = "my_Second_Popup" Set cbctl = cbsub.Controls.Add(Type:=msoControlButton) cbctl.Visible = True cbctl.Style = msoButtonCaption cbctl.Caption = "my_firt_button_in_the_second_popup" cbctl.OnAction = "" End Sub "Greg Wilson" wrote: The answer is no. However, instead of using a msoControlPopup, you can create a popup type toolbar and call it using a regular msoControlButton (which supports a FaceId). The affect is about the same. Note that this method is sometimes advantagious because the macro can be used to change the status of controls on the popup toolbar (e.g. enabled property etc.) before showing the toolbar; whereas the click event of the msoControlPopup cannot be accessed before the dropdown (or popup toolbar ?) is displayed. You would have to use other, less convenient, events to initiate change of the control properties. Regards, Greg "Troispieds" wrote: Hi All, it is possible to add an image (icon) to the msoControlPopup, like it is the case for the msoControlButton ?For answers thanks in advance. Cheers, Florian "Greg Wilson" wrote: For the sake of brevity, I only show how to: 1) Create a new toolbar 2) Add a popup type control (that supports menu items) to the toolbar 2) Add menu items to the popup control (menu control) 3) Add a popup control (that supports submenu items) to the first popup control 4) Add submenu items to the second popup control 5) Use arrays and loops to drastically cut down on the code requirement. You can continue the same branching process indefinately to my knowledge. Regards, Greg Sub MakeNewMenu() Dim CB As CommandBar Dim NewMenu As CommandBarControl Dim MenuItm As CommandBarControl Dim SubMenuItm As CommandBarControl Dim Arr1 As Variant, Arr2 As Variant Dim Arr3 As Variant Dim i As Integer With Application .ScreenUpdating = False 'Menu item caption list Arr1 = Array("Caption 1", "Caption 2", _ "Caption 3", "Caption 4", "Caption 5") 'Menu item macro list Arr2 = Array("Menu macro 1", "Menu macro 2", _ "Menu macro 3", "Menu macro 4", "Menu macro 5") 'Menu item FaceId list Arr3 = Array(100, 101, 102, 103, 104) Set CB = .CommandBars.Add("Data Analysis", Temporary:=True) Set NewMenu = CB.Controls.Add(msoControlPopup) NewMenu.Caption = "Menu items" 'ToolTipText supported on this level only NewMenu.TooltipText = "Select from my macros" 'When referencing elements in an array, the first 'element has an index value of zero (hence 0 to 4) For i = 0 To 4 Set MenuItm = NewMenu.Controls.Add With MenuItm .Caption = Arr1(i) .Style = msoButtonIconAndCaption .OnAction = Arr2(i) .FaceId = Arr3(i) End With Next 'Submenu item caption list Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _ "Caption 9", "Caption 10") 'Submenu item macro list Arr2 = Array("Submenu macro 1", "Submenu macro 2", _ "Submenu macro 3", "Submenu macro 4", "Submenu macro 5") 'Submenu item FaceId list Arr3 = Array(200, 201, 202, 203, 204) 'Now add popup type control to support the submenus Set MenuItm = NewMenu.Controls.Add(msoControlPopup) MenuItm.Caption = "Sub menu items" For i = 0 To 4 Set SubMenuItm = MenuItm.Controls.Add With SubMenuItm .Caption = Arr1(i) .Style = msoButtonIconAndCaption .OnAction = Arr2(i) .FaceId = Arr3(i) End With Next CB.Visible = True DoEvents .ScreenUpdating = True End With End Sub "Anders" wrote: Hi all, I would like to create a command bar that contains a menu with sub menues. Do anyone know how to do this? Menu ------- Menu Item1 Menu Item2 - Sub menu item - Sub sub menu item Regards Anders |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sub menu in Command bar
This was done very quickly and tested briefly. I'm short on time today.
It shows that you can use a standard commandbar button to display a popup type toolbar (Position:=msoBarPopup) in the place of a standard menu type control (msoControlPopup). I use a loop to populate the popup toolbar and use arrays to apply Caption, OnAction and FaceId properties. I also show that this method has the advantage that you can change the status (e.g. Enabled property) of controls before displaying the popup. I repeat, this was done quickly. My tests indicate that it's OK. Best of luck !!! Regards, Greg Sub XYZ() Dim CBar As CommandBar, Popup As CommandBar Dim ctrl As CommandBarControl Dim CaptArr As Variant, MacroArr As Variant Dim FaceIdArr As Variant Dim i As Long With Application .ScreenUpdating = False Set CBar = .CommandBars("myToolbar") Set ctrl = CBar.Controls.Add(Temporary:=True) With ctrl .Caption = "Options" .TooltipText = "Show Option list" .OnAction = "ShowPopup" .FaceId = 300 .Style = msoButtonIconAndCaption End With On Error Resume Next .CommandBars("Options List").Delete On Error GoTo 0 Set Popup = .CommandBars.Add(Position:=msoBarPopup, Temporary:=True) Popup.Name = "Options List" CaptArr = Array("Run Macro 1", "Run Macro 2", "Run Macro 3") MacroArr = Array("Macro1", "Macro2", "Macro3") FaceIdArr = Array(100, 101, 102) For i = 0 To 2 Set ctrl = Popup.Controls.Add With ctrl .Caption = CaptArr(i) .OnAction = MacroArr(i) .FaceId = FaceIdArr(i) End With Next CBar.Visible = True .ScreenUpdating = True End With End Sub Private Sub ShowPopup() Dim i As Long 'This demo toggles the enabled status of the 'second button before showing the popup commandbar With Application.CommandBars("Options List") .Controls(2).Enabled = Not .Controls(2).Enabled .ShowPopup End With End Sub Sub Macro1() MsgBox "You called Macro1" End Sub Sub Macro2() MsgBox "You called Macro2" End Sub Sub Macro3() MsgBox "You called Macro3" End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sub menu in Command bar
This code looks like what I am after, but when I ran it I get an Error 5 at:
Set CBar = .CommandBars("myToolbar") Does anyone know why? Do I need something outside of the below to get this to work? -- Trefor "Greg Wilson" wrote: This was done very quickly and tested briefly. I'm short on time today. It shows that you can use a standard commandbar button to display a popup type toolbar (Position:=msoBarPopup) in the place of a standard menu type control (msoControlPopup). I use a loop to populate the popup toolbar and use arrays to apply Caption, OnAction and FaceId properties. I also show that this method has the advantage that you can change the status (e.g. Enabled property) of controls before displaying the popup. I repeat, this was done quickly. My tests indicate that it's OK. Best of luck !!! Regards, Greg Sub XYZ() Dim CBar As CommandBar, Popup As CommandBar Dim ctrl As CommandBarControl Dim CaptArr As Variant, MacroArr As Variant Dim FaceIdArr As Variant Dim i As Long With Application .ScreenUpdating = False Set CBar = .CommandBars("myToolbar") Set ctrl = CBar.Controls.Add(Temporary:=True) With ctrl .Caption = "Options" .TooltipText = "Show Option list" .OnAction = "ShowPopup" .FaceId = 300 .Style = msoButtonIconAndCaption End With On Error Resume Next .CommandBars("Options List").Delete On Error GoTo 0 Set Popup = .CommandBars.Add(Position:=msoBarPopup, Temporary:=True) Popup.Name = "Options List" CaptArr = Array("Run Macro 1", "Run Macro 2", "Run Macro 3") MacroArr = Array("Macro1", "Macro2", "Macro3") FaceIdArr = Array(100, 101, 102) For i = 0 To 2 Set ctrl = Popup.Controls.Add With ctrl .Caption = CaptArr(i) .OnAction = MacroArr(i) .FaceId = FaceIdArr(i) End With Next CBar.Visible = True .ScreenUpdating = True End With End Sub Private Sub ShowPopup() Dim i As Long 'This demo toggles the enabled status of the 'second button before showing the popup commandbar With Application.CommandBars("Options List") .Controls(2).Enabled = Not .Controls(2).Enabled .ShowPopup End With End Sub Sub Macro1() MsgBox "You called Macro1" End Sub Sub Macro2() MsgBox "You called Macro2" End Sub Sub Macro3() MsgBox "You called Macro3" End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sub menu in Command bar
sounds like you don't have a toolbar called myToolbar
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Trefor" wrote in message ... This code looks like what I am after, but when I ran it I get an Error 5 at: Set CBar = .CommandBars("myToolbar") Does anyone know why? Do I need something outside of the below to get this to work? -- Trefor "Greg Wilson" wrote: This was done very quickly and tested briefly. I'm short on time today. It shows that you can use a standard commandbar button to display a popup type toolbar (Position:=msoBarPopup) in the place of a standard menu type control (msoControlPopup). I use a loop to populate the popup toolbar and use arrays to apply Caption, OnAction and FaceId properties. I also show that this method has the advantage that you can change the status (e.g. Enabled property) of controls before displaying the popup. I repeat, this was done quickly. My tests indicate that it's OK. Best of luck !!! Regards, Greg Sub XYZ() Dim CBar As CommandBar, Popup As CommandBar Dim ctrl As CommandBarControl Dim CaptArr As Variant, MacroArr As Variant Dim FaceIdArr As Variant Dim i As Long With Application .ScreenUpdating = False Set CBar = .CommandBars("myToolbar") Set ctrl = CBar.Controls.Add(Temporary:=True) With ctrl .Caption = "Options" .TooltipText = "Show Option list" .OnAction = "ShowPopup" .FaceId = 300 .Style = msoButtonIconAndCaption End With On Error Resume Next .CommandBars("Options List").Delete On Error GoTo 0 Set Popup = .CommandBars.Add(Position:=msoBarPopup, Temporary:=True) Popup.Name = "Options List" CaptArr = Array("Run Macro 1", "Run Macro 2", "Run Macro 3") MacroArr = Array("Macro1", "Macro2", "Macro3") FaceIdArr = Array(100, 101, 102) For i = 0 To 2 Set ctrl = Popup.Controls.Add With ctrl .Caption = CaptArr(i) .OnAction = MacroArr(i) .FaceId = FaceIdArr(i) End With Next CBar.Visible = True .ScreenUpdating = True End With End Sub Private Sub ShowPopup() Dim i As Long 'This demo toggles the enabled status of the 'second button before showing the popup commandbar With Application.CommandBars("Options List") .Controls(2).Enabled = Not .Controls(2).Enabled .ShowPopup End With End Sub Sub Macro1() MsgBox "You called Macro1" End Sub Sub Macro2() MsgBox "You called Macro2" End Sub Sub Macro3() MsgBox "You called Macro3" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Command to RightClick Menu | Excel Programming | |||
Add Command to Right-Click Menu | Excel Programming | |||
there is no XML command on my data menu | Excel Worksheet Functions | |||
Command Bar Menu - Management | Excel Programming | |||
Execute a menu command with VBA? | Excel Programming |