Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call a UserForm based on ComboBox selection
Ive 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call a UserForm based on ComboBox selection
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" wrote in message ... Ive 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call a UserForm based on ComboBox selection
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" wrote in message ... Ive 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call a UserForm based on ComboBox selection
Exactly what are options A and B supposed to do?
Rick "DJ" wrote in message ... 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" wrote in message ... Ive 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call a UserForm based on ComboBox selection
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" wrote in message ... 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" wrote in message ... Ive 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate Sheet based on ComboBox selection | Excel Programming | |||
How to populate a combobox based on selection from another combobo | Excel Programming | |||
VB Script based on combobox selection | Excel Programming | |||
Userform: Textbox changing with selection in combobox (list) | Excel Programming | |||
Excel - Copy range of cells based on Combobox Selection | Excel Programming |