Load Userform using ComboBox

[BrianExcel]

When a user hits a button I am bringing up a small Userform with a ComboBox on it.

The values for that combobox (currently) are UserForm1 and UserForm2.

What I am trying to do is, when the user selects the value from the combobox, that value is passed into the load userform command, but it's erroring out on me.

This is the code I am trying to use, but it's not working. Can anyone assist?

Sub ComboBox1_Change()
Dim sFormName As String

sFormName = UserForm3.ComboBox1.Value

    Load sFormName
    sFormName.Show
End Sub

 

[Norie]

The show method needs an object, as far as I can see sFormName is a string.

I think if you wanted to do anything like this you might need to look into using the UserForms collection.

What is it you are trying to do anyway?

Are the 2 forms completely different?

 

[BrianExcel]

Yes, just as an experiment I created 2 or 3 differnet forms. They all contain the same info but in different formats, layouts, etc.

I wanted to have a simple interface where they could choose.

If the user chooses "UserForm2" from the combo box, then UserForm2 opens. If they choose "UserForm3" then UserForm3 opens, and so on.

Make sense? It's not practical from a long-term perspective but I need to know how to make this work for a demo.

 

[Norie]

Why not just have separate subs for each userform?

Then you could use something like Application.Run to run the appropriate sub based on the selection in the combobox.

Or a Select Case structure, again based around what's selected from the combobox.

That wouldn't be too difficult to set up.

 

[Rasm]

Maybe something along this line - ohhh should have been private subs - but no biggie

Sub ComboBox1_Change()
Dim sFormName As String
sFormName = UserForm3.ComboBox1.Value
    Select Case sFormName
        Case "NameUhave1"
            Call LoadForm1
        Case "NameUhave2"
            LoadForm2
    End Select
    
End Sub
Public Sub LoadForm1()
    Load FormName1
    FormName1.Show
End Sub
Public Sub LoadForm2()
    Load FormName1
    FormName1.Show
End Sub

 

[BrianExcel]

Thanks Rasm. This worked well. Had a little trouble because the forms were set as modal but once I adjusted that it worked fine.

 

[RoryA]

Userforms.add(sformname).show

should also work.