Using a variable to open a userform

[andrewb90]

Hi all,

I have a number of similarly named user forms that are called based on the value in a cell. I am trying to find a more efficient way to call the user forms without having to add additional elseifs to the code everytime I add a few more. I thought I had the right idea, but perhaps I am not using my variables correctly.

Sub RedB()
Dim bnum As Variant: Dim bmod As Variant
bnum = Sheets(1).Range("AA2").Value
bmod = "Red_B" & bnum
bmod.Show (False)
Exit Sub

End Sub

This code is the small portion that I currently have and it works, but I'd like to eliminate the need to add additional statements to the IF:

If Sheets(1).Range("AA2").Value = 1 Then
    Red_B1.Show (False)
ElseIf Sheets(1).Range("AA2").Value = 2 Then
    Red_B2.Show (False)
ElseIf Sheets(1).Range("AA2").Value = 3 Then
    Red_B3.Show (False)
ElseIf Sheets(1).Range("AA2").Value = 4 Then
    Red_B4.Show (False)
End If

Any help in properly correcting this code would be much appreciated!

 

[dmt32]

Hi,
try using the CallByName Function & see if does what you want

Sub RedB()
    Dim bnum As Variant, bmod As Variant
    Dim Form As Object
    
    bnum = Sheets(1).Range("AA2").Value
    bmod = "Red_B" & bnum

    Set Form = CallByName(UserForms, "Add", VbMethod, bmod)
    Form.Show

End Sub

You can read more in the VBA Helpfile: CallByName function (Visual Basic for Applications)

 

[andrewb90]

Hi,
try using the CallByName Function & see if does what you want

Sub RedB()
    Dim bnum As Variant, bmod As Variant
    Dim Form As Object
   
    bnum = Sheets(1).Range("AA2").Value
    bmod = "Red_B" & bnum

    Set Form = CallByName(UserForms, "Add", VbMethod, bmod)
    Form.Show

End Sub

You can read more in the VBA Helpfile: CallByName function (Visual Basic for Applications)

Awesome! That was what I needed. Thank you!

 

[dmt32]

Awesome! That was what I needed. Thank you!

most welcome & appreciate your feedback

Dave