CODEWITHSUNDEEP
excelvba
SilentRevolution
SilentRevolution

Reputation: 1513

Show form based on variable string input

Is it possible to show a form like Form_1.show based on a string variable like with the worksheets or controls like such

Private Sub Demo()
    Dim i As Long
    For i = 1 To 10
        me.Controls("Label_" & i) = Rnd()
    Next i

    'Or select a worksheet based on an input

    With Me.ListBox1
        For i = 1 to 5
            .additem Cstr("Sheet" & i)
        Next i
    End With

    ThisWorkBook.Worksheets(me.ListBox1).Activate
End Sub

Basically what I'm trying to do is show Form1 to 10 based on a choice made from a listbox in a form and instead of writing a lot of If statements is it possible to change the form which is shown based on a choice?

Below is the current situation.

Private Sub Worksheet_Activate()
    Dim Table1 as ListObject
    Set Table1 = Thisworkbook.Worksheets("Sheet1").Listobjects("Forms_List")
    Dim Rows As Long
    Rows = Table1.ListRows.Count
    Dim i As Long

    With Me.ListBox1
        For i = 1 To Rows
            .AddItem Table1.DataBodyRange(i, 1)
        Next i
    End With
End Sub

Private Sub ListBox1_dblclick(ByVal Cancel As MSForms.ReturnBoolean) 
    'This is what I want to avoid, mainly because it isn't flexible
    If Me.ListBox1 = "Form1" Then Form1.Show
    If Me.ListBox1 = "Form2" Then Form2.Show
    If Me.ListBox1 = "Form3" Then Form3.Show
    If Me.ListBox1 = "Form4" Then Form4.Show
    If Me.ListBox1 = "Form5" Then Form5.Show
    If Me.ListBox1 = "Form6" Then Form6.Show
    If Me.ListBox1 = "Form7" Then Form7.Show
    If Me.ListBox1 = "Form8" Then Form8.Show
    If Me.ListBox1 = "Form9" Then Form9.Show
    If Me.ListBox1 = "Form10" Then Form10.Show
End Sub

Upvotes: 1

Views: 475

Answers (1)

Fadi
Fadi

Reputation: 3322

Try using this :

VBA.UserForms.Add(Me.ListBox1).Show

Source: How to Display a UserForm Whose Name Is in a Variable

Upvotes: 2

Related Questions