Article ID: 157609 - Last Review: November 23, 2006 - Revision: 2.4

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

View products that this article applies to.

This article was previously published under Q157609

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.

Back to the top

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms) 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

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

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

to display UserForm1.

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

Run-time error '424'
Object Required

Back to the top

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

Back to the top

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

Back to the top

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

Back to the top

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

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

Run-time error '424'

and your macro will halt.

Back to the top

APPLIES TO

Microsoft Excel 97 Standard Edition

Back to the top

kbdtacode kberrmsg kbfaq kbhowto kbprogramming KB157609

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top

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

On This Page

SUMMARY

MORE INFORMATION

UserForm Name Stored in a Variable

User Prompted for UserForm Name

UserForm Name Retrieved from Worksheet Cell

Prompt for Index Number to Display UserForm

APPLIES TO

Keywords:

Provide feedback on this information

Get Help Now

Article Translations

Page Tools

Get Help Now