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?

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?

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?