Button controlled "self populating PopUP" menu

T

Tom-n-Nash

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?
 
J

Jacob Skaria

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
 
T

Tom-n-Nash

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top