XL98: How to Display a UserForm with a Name That Is in a Variable (182411)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. In all following examples, the UserForm being used is called UserForm1.

In Microsoft Excel 98 Macintosh Edition, if you want a Visual Basic macro to display UserForm1, the line of code to use is the following:

   UserForm1.Show
				

Although this syntax is very simple, it can cause problems if you want to show a UserForm with a name that is stored in a variable. For example, if the variable "x" contains the name of the UserForm, you cannot simply use the following procedure

   Sub Test()
       x = "UserForm1"
       x.Show
   End Sub
				

to display UserForm1.

If you run this macro, you receive the following error message:

UserForm Name Stored in a Variable

If you want to show a UserForm with a name that is stored in a variable, use the following code:

   Sub ShowUserForm1()
       X = "UserForm1"
       VBA.UserForms.Add(X).Show
   End Sub
				

User Prompted for UserForm Name

To ask a user what UserForm to display, use the InputBox method as in the following procedure:

   Sub ShowUserForm2()
       X = InputBox("Show which UserForm?: ")
       VBA.UserForms.Add(X).Show
   End Sub
				

UserForm Name Retrieved from Worksheet Cell

You can read a value from a cell in a worksheet by using the following procedure:

  Sub ShowUserForm3()

       'Cell A1 of Sheet1 contains the name of the UserForm
       X = ActiveWorkbook.Sheets("Sheet1").Cells(1, 1).Value

       VBA.UserForms.Add(X).Show
   End Sub
				

Prompt for Index Number to Display UserForm

You can include a list of UserForm names in your macro and set the value of X to the UserForm that corresponds to an index number.

NOTE: The following example assumes that there are three UserForms in the project: UserForm1, UserForm2, and UserForm3. You may want to insert some macro code to make sure the value entered is between 1 and 3, inclusive.

    Sub ShowUserForm4()

       'Display an input box asking for a number between 1 and 3
       '(inclusive).
       Y = Application.InputBox (Prompt:="enter 1, 2, or 3", Type:=1)

       'Based on the number entered in the input box, X will be set to
       'the appropriate string.

       Select Case Y
           Case 1
               X = "UserForm1"
           Case 2
               X = "UserForm2"
           Case Else
               X = "UserForm3"
       End Select

       VBA.UserForms.Add(X).Show
   End Sub
				

In each case above, the UserForm you want to show must exist. If it does not exist in the project, you receive the following error message and the macro halts.