Hi Rick,
Options A and B are the options listed in the ComboBox. Secifically, the
ComboBox (named "Options") shows 8 different types of products -- for most of
these product types, we have only one item. However, for a couple of these
product types, we have 6 items. The idea is to keep the lists from getting
too long, and when a product type is selected for which we have several
offerings, the UserForm appears with the next-level of choices.
I think my problem is that I don't know how to refer to the user's selection
in the ComboBox. For example, the following simpler macro (a test to see if
I'm referring to things properly) just returns a message box that says "your
selection is".
Private Sub Options_Click()
MsgBox "your selection is " & Selection.Text
End Sub
How do I make it so when the user selects "A" from the ComboBox drop-down
list, the message box will say "your selection is A"?
Thanks for any ideas you can provide!
DJ
"Rick Rothstein (MVP - VB)" wrote:
> Exactly what are options A and B supposed to do?
>
> Rick
>
>
> "DJ" <(E-Mail Removed)> wrote in message
> news:25829ED4-6C16-4C16-AF09-(E-Mail Removed)...
> > Hi Chip,
> > I tried the simpler idea, and got no respone (no error, no UserForm). Do
> > all possible cases need to be listed in the macro?
> >
> > DJ
> >
> > "Chip Pearson" wrote:
> >
> >> Try something like
> >>
> >> Select Case UCase(Selection.Text)
> >> Case "A"
> >> Userform1.Show
> >> Case "B"
> >> Userform2.Show
> >> ' and so on
> >> End Select
> >>
> >> If you want to get a bit trickier, you can use code like
> >>
> >> Sub AAA()
> >> Dim N As Long
> >> Dim Obj As Object
> >> N = 3
> >> Select Case N
> >> Case 1
> >> Set Obj = VBA.UserForms.Add("Userform1")
> >> Case 2
> >> Set Obj = VBA.UserForms.Add("Userform2")
> >> Case 3
> >> Set Obj = VBA.UserForms.Add("Userform3")
> >> End Select
> >> If Not Obj Is Nothing Then
> >> Obj.Show
> >> End If
> >> End Sub
> >>
> >> See http://www.cpearson.com/Excel/showanyform.htm for an explanation.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft Most Valuable Professional
> >> Excel Product Group
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email on web site)
> >>
> >>
> >>
> >>
> >>
> >>
> >> "DJ" <(E-Mail Removed)> wrote in message
> >> news:07C6C04C-3423-4A2B-937F-(E-Mail Removed)...
> >> > I’ve created a worksheet ComboBox that contains options A, B, and C.
> >> > When
> >> > option C is selected, I would like UserForm1 to appear. I am having
> >> > trouble
> >> > calling the user form. I have tried variations of the following:
> >> >
> >> > Private Sub Options_Click()
> >> > 'If TypeName(Selection) = "C" Then Load UserForm1
> >> > End Sub
> >> >
> >> > Any help is much appreciated!
> >> > DJ
> >>
>
>
Similar Threads
