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

• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel 97, it is possible for a Visual Basic for Applications macro to display a UserForm whose name is stored in a variable. This article explains how this may be done.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

In all following examples, the UserForm being used is called "UserForm1" (without the quotation marks).

In Microsoft Excel 97, if you want a Visual Basic macro to display UserForm1, the line of code would be:

   UserForm1.Show

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

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

   Run-time error '424'
   Object Required

UserForm Name Stored in a Variable

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

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

User Prompted for UserForm Name

You can use the InputBox method to ask users what UserForm they want to show:

   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:

   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 you have 3 UserForms in your 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

   Run-time error '424'