Call a UserForm based on ComboBox selection

[DJ]

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

 

[Chip Pearson]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

Exactly what are options A and B supposed to do?

Rick

 

[DJ]

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