CODEWITHSUNDEEP
vbaexcel
VBA Pete
VBA Pete

Reputation: 2666

Call Userform based on Userform Value in cell

I have a table with the following values:

enter image description here

Now, I would like to call the Userform in column H based on the value in column G, but I can't work out how to call the Userform based on the cell value. The error occurs in line

form.Name = wsControls.Cells(loop2, 8).Value

Here is my code:

Sub Check_Scenarios()
Dim wsAbsatz As Worksheet
Dim wsControls As Worksheet
Dim wsData As Worksheet
Dim loop1 As Long
Dim loop2 As Long
Dim lngKW As Long
Dim form As UserForm

Set wsAbsatz = ThisWorkbook.Worksheets("Production")
Set wsData = ThisWorkbook.Worksheets("Data")
Set wsControls = ThisWorkbook.Worksheets("Controls")

lngKW = wsControls.Cells(1, 2).Value + 2

If lngKW = 3 Then
    Exit Sub
End If


For loop1 = wsControls.Cells(10, 2).Value To wsControls.Cells(19, 2).Value Step 10
    If wsData.Cells(loop1 + 3, lngKW).Value <> "" Then
        MsgBox (wsData.Cells(loop1 + 3, lngKW).Value)
        For loop2 = 2 To 16
            If wsData.Cells(loop1 + 3, lngKW).Value = wsControls.Cells(loop2, 7).Value Then
                form.Name = wsControls.Cells(loop2, 8).Value 'error occurs here
                form.Show
            End If
        Next loop2
    End If
Next loop1

End Sub

Project:

enter image description here

Many thanks for your help!

Upvotes: 1

Views: 1768

Answers (2)

Vityata
Vityata

Reputation: 43593

You are trying to assign a Name to a blueprint. These are two errors.

You have to initialize your blueprint as something. Like this:

Dim form As New UserForm Then, most probably your UserForm does not have a property called Name. It is called Caption. Thus it is like this:

Sub TestMe()

    Dim uf As New UserForm1 'judging from your screenshot
    uf.Caption = "Testing"
    uf.Show

End Sub

Disclaimer: There is a better way to work with UserForms, not abusing the blueprint, although almost every VBA book shows this UserForm.Show method (in fact every single one I have read so far).

If you have the time and the OOP knowledge implement the ideas from here - or from my interpretation of the ideas. There was also a documentation article about it in StackOverflow, but it was deleted with the whole documentation idea.

Upvotes: 3

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

You don't "call" a userform. You instantiate it, and then you Show it.

UserForm is the "base class" from which all userforms are derived. See there is inheritance in VBA, only not with custom classes.

So you have a UserForm2 class, a UserForm3 class, a UserForm4 class, and so on.

These classes need to be instantiated before they can be used.

Dim theForm As UserForm
Set theForm = New UserForm2
theForm.Show

Set theForm = New UserForm3
theForm.Show

'...

So what you need is a way to parameterize this Set theForm = New ????? part.

And you can't. Because whatever you're going to do, the contents of a cell is going to be a string, and there's no way you can get an instance of a UserForm3 out of a String that says "UserForm3".

Make a factory function that does the translation:

Public Function CreateForm(ByVal formName As String) As UserForm
    Select Case formName
        Case "UserForm1"
            Set CreateForm = New UserForm1
        Case "UserForm2"
            Set CreateForm = New UserForm2
        Case "UserForm3"
            Set CreateForm = New UserForm3
        '...
    End Select
End Function

And then call that function to get your form object:

Set form = CreateForm(wsControls.Cells(loop2, 8).Value)
If Not form Is Nothing Then form.Show

Upvotes: 2

Related Questions