Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button controlled "self populating PopUP" menu
My project has 3 buttons on the User page that "pop up" a menu (shortcut
type) when clicked. On one of the menus, I need the sub-options for a primary option to be generated when the button is clicked. In other words, it should pull information from a range of cells on another page. This range varies depending on previous information gathered through the use of userforms. I have seen menus that do this, and have seen a similar example in a book, but can't figure out how to modify the code for a popup menu. How can I include code in my menu creation code that allows something like: for each c in sheets??range(XY)... .AddItem.c then use this with List(ListIndex) to make a menu use that info to make a list of sub-options? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button controlled "self populating PopUP" menu
If you mean the popup menu; try the below
On the active sheet Range("A1:A10") type some values say 1 to 10 and run the below macro.. Sub Macro() Dim cell As Range Dim cbCTLPop As CommandBarPopup, cbCTLBut As CommandBarButton On Error Resume Next Application.CommandBars("MyBar").Delete Application.CommandBars.Add "MyBar", Position:=msoBarPopup, _ Temporary:=True Set cbCTLPop = Application.CommandBars("MyBar").Controls.Add( _ Type:=msoControlPopup, Temporary:=True) cbCTLPop.Caption = "My menu" For Each cell In Range("A1:A10") Set cbCTLBut = cbCTLPop.Controls.Add(Temporary:=True) With cbCTLBut .Caption = cell.Text .Style = msoButtonCaption .OnAction = "Macro_" & cell.Text End With Set cbCTLBut = Nothing Next Application.CommandBars("MyBar").ShowPopup End Sub If this post helps click Yes --------------- Jacob Skaria "Tom-n-Nash" wrote: My project has 3 buttons on the User page that "pop up" a menu (shortcut type) when clicked. On one of the menus, I need the sub-options for a primary option to be generated when the button is clicked. In other words, it should pull information from a range of cells on another page. This range varies depending on previous information gathered through the use of userforms. I have seen menus that do this, and have seen a similar example in a book, but can't figure out how to modify the code for a popup menu. How can I include code in my menu creation code that allows something like: for each c in sheets??range(XY)... .AddItem.c then use this with List(ListIndex) to make a menu use that info to make a list of sub-options? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button controlled "self populating PopUP" menu
Thank you so much Jacob.. I've been trying for days to come up with this!
It's exactly what I need. You Terrific! Tom "Jacob Skaria" wrote: If you mean the popup menu; try the below On the active sheet Range("A1:A10") type some values say 1 to 10 and run the below macro.. Sub Macro() Dim cell As Range Dim cbCTLPop As CommandBarPopup, cbCTLBut As CommandBarButton On Error Resume Next Application.CommandBars("MyBar").Delete Application.CommandBars.Add "MyBar", Position:=msoBarPopup, _ Temporary:=True Set cbCTLPop = Application.CommandBars("MyBar").Controls.Add( _ Type:=msoControlPopup, Temporary:=True) cbCTLPop.Caption = "My menu" For Each cell In Range("A1:A10") Set cbCTLBut = cbCTLPop.Controls.Add(Temporary:=True) With cbCTLBut .Caption = cell.Text .Style = msoButtonCaption .OnAction = "Macro_" & cell.Text End With Set cbCTLBut = Nothing Next Application.CommandBars("MyBar").ShowPopup End Sub If this post helps click Yes --------------- Jacob Skaria "Tom-n-Nash" wrote: My project has 3 buttons on the User page that "pop up" a menu (shortcut type) when clicked. On one of the menus, I need the sub-options for a primary option to be generated when the button is clicked. In other words, it should pull information from a range of cells on another page. This range varies depending on previous information gathered through the use of userforms. I have seen menus that do this, and have seen a similar example in a book, but can't figure out how to modify the code for a popup menu. How can I include code in my menu creation code that allows something like: for each c in sheets??range(XY)... .AddItem.c then use this with List(ListIndex) to make a menu use that info to make a list of sub-options? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
Stop users from accessing "Protection" option from "Tools" menu | Excel Programming | |||
CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = Fals | Excel Programming | |||
What does the "Paste Special Dropdown" popup menu do? | Excel Programming | |||
How do I get events from a "Split Button Popup" control? | Excel Programming |