CODEWITHSUNDEEP
excelvbauserform
david_r
david_r

Reputation: 11

How to open a Userform using a variable in the Name?

I'm trying to open UserForms based on the values of cells in one row of a sheet. There are 17 UserForms so I don't want to have to use 17 if statements for each form like this:

If ActiveCell.Value = 1 Then
     UserForm1.Show
End If

Is there a way that I can use a variable to show the forms?

I was thinking something along the lines of:

Dim i
Do
   If ActiveCell.Value = "" Then
      Exit DO
   End If
   i = ActiveCell.Value
   UserForms("UserForm" & i).Show   ****THIS is what doesn't work
   ActiveCell.Offset(0,1).Select
Loop

Upvotes: 1

Views: 12728

Answers (6)

X-a4
X-a4

Reputation: 1

Here a "faster" code to open the form (Rao-Haribabu evolution):

Dim forMy
Set forMy = CallByName(UserForms, "Add", VbMethod, formName) ' formName is the form name to open
forMy.Show

Upvotes: 0

Haribabu G L
Haribabu G L

Reputation: 11

Get UserForm object defined by its string name

Function Form(Name As String) As Object
  Set Form = CallByName(UserForms, "Add", VbMethod, Name)
End Function

Sub Test()
  Dim strFormName As String
  strFormName = "UserForm1"  ' <-- replace by your lookup code instead
  Form(strFormName).Show
End Sub

Upvotes: 1

Juan Joya
Juan Joya

Reputation: 118

The easiest way is this one:

Dim activeuf as Object
Set activeuf = UserForm & i
activeuf.show

Perhaps it will not work for the person who asked, but I'm sure it will help people who check this question in the future

Upvotes: 0

Davy C
Davy C

Reputation: 649

Paste the code from the link Harvey provided, then adjust this line in your code:

UserForms("UserForm" & i).Show  ****THIS is what doesn't work

to:

ShowAnyForm ("UserForm" & i)

That's a great link Harvey, I've bookmarked it!

Upvotes: 2

PangolinPaws
PangolinPaws

Reputation: 18

I've never come accross the method mentioned by @Harvey (I like it, though) so would have used some sort of Select Case statement:

Select Case .Cells(1,1).Value
    Case 1:
        FormOne.Show
    Case 2:
        FormTwo.Show
    ' And so on and do forth...
    Case Else:
        MsgBox ("Invalid entry")
End Select

Simpler than 17 If statements, at least.

Upvotes: 0

HarveyFrench
HarveyFrench

Reputation: 4568

You can use the often overlooked VBA.UserForms object. See this link which fully descibes what you need to do.

There's no point in my explaining it here.

Harvey

Upvotes: 1

Related Questions