ComboBox to select UserForm

[sgp]

Hi ALL

I am trying to show a ComboBox in Excel which should allow the user to select the required UserForm. My coding does not work, even though I have used the same coding before.

Please see the attached example.

Thanks a lot!

[JieJenn]

Something similar to this?

[john55]

hi sgp,
I use the snb's code for it, it works for me, try the attachement

[sgp]

Thanks for your help guys

The way the code is written shows all userforms in one combo box - could this be adjusted so that combobox1 shows xyz userforms and combobox 2 shows 123 userforms.

Thanks a lot!

[nilem]

or

Private Sub Workbook_Open()
Dim cp As Object
For Each cp In ThisWorkbook.VBProject.VBComponents
        If cp.Type = 3 Then c01 = c01 & "|" & cp.Name
    Next
Sheets("Sheet1").ComboBox1.List = Split(Mid(c01, 2), "|")
Set cp = Nothing
End Sub

and

Private Sub ComboBox1_Change()
Select Case ComboBox1.ListIndex
    Case 0
        UserForm1.Show 0
    Case 1
        UserForm2.Show 0
End Select
End Sub

[royUK]

What reason do you have for multiple UserForms?

[sgp]

I have userforms based on different categories. I would like the following combobox:

1- where the user can select the main categories
2 - lists of categories that are not classed as the main categories.

[royUK]

I think you would be better with one userform & a select categories on that userform, it's really hard to advise without knowing what the end project is

[sgp]

Thanks for the suggestion - the forms are slightly detailed and I dont think I would be able to fit everything on one form.

[royUK]

Then a MultiPage control. Usually a a UserForm is used for Data entry so really I would expect the data to be similar

[john55]

or using the snb'code ....see if it helps you

Private Sub Workbook_Open()
For Each cp In ThisWorkbook.VBProject.VBComponents
        If cp.Type = 3 Then c01 = c01 & "|" & cp.Name
    Next
    Sheets("Sheet1").ComboBox2.List = Array("UserForm1", "UserForm2") ''Split(Mid(c01, 2), "|")
    
     Sheets("Sheet1").ComboBox3.List = Array("UserForm3", "UserForm4") ''Split(Mid(c01, 2), "|")
     
End Sub

and the code for comboboxes

Private Sub ComboBox2_Change()
Select Case ComboBox2.ListIndex
    Case 0
      UserForm1.Show
      
      Case 1
      UserForm2.Show
      
      End Select
      
End Sub

Private Sub ComboBox3_Change()
Select Case ComboBox3.ListIndex
    Case 0
      UserForm3.Show
    Case 1
      UserForm4.Show
            End Select

End Sub

[sgp]

Hi john

That works brilliantly - thanks a lot!

[john55]

hi sgp,
glad to be of help, I've just modified a little bit the snb's code.