poping up a box with choices?

N

neowok

just wondering if its possible to pop up a box with say 3 choices in it
user selects one of these and an action can then be performed based o
their choice.

i.e. ive got some ovals with text in them, some of these have severa
ref numbers and it would be useful if a user clicks on one of the oval
they get a choice of which number they want. i can then display th
corresponding data row once ive got their choice
 
D

Dick Kusleika

neowok

How about a menu on the right click menu for the shape. You assign the
macro ShowMenu to all the shapes for which you want the menu to appear. One
problem is that you have to left click on the shape before you right click
to the get the menu, or the menu won't show the right data.

Dim aNums As Variant

Sub ShowMenu()

Dim cb As CommandBar
Dim cbb As CommandBarControl
Dim ctl As CommandBarControl
Dim i As Long

Set cb = Application.CommandBars("shapes")

If cb.Controls(1).Caption = "Select Number" Then
Set cbb = cb.Controls(1)
For Each ctl In cbb.Controls
ctl.Delete
Next ctl
'cbb.Reset
Else
Set cbb = cb.Controls.Add(msoControlPopup, , , 1, True)
cbb.Visible = True
cbb.Caption = "Select Number"
End If


aNums = Split(Sheet1.Shapes(Application.Caller).TextFrame. _
Characters(1, 255).Text, ",", , vbTextCompare)

For i = LBound(aNums) To UBound(aNums)
With cbb.Controls.Add(msoControlButton)
.Caption = Trim(aNums(i))
.OnAction = "CatchNumber"
.Style = msoButtonCaption
.Visible = True
End With
Next i

End Sub

Sub CatchNumber()

MsgBox aNums(Application.Caller(1) - 1)

End Sub
 
Top