Microsoft KB Archive/182411

= XL98: How to Display a UserForm with a Name That Is in a Variable =

Article ID: 182411

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q182411



SUMMARY
In Microsoft Excel 98 Macintosh Edition, it is possible for a Visual Basic for Applications macro to display a UserForm with a name that is stored in a variable. This article explains how to do this.



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:

Run-time error '424'

Object Required

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

Run-time error '424'

and the macro halts.

Additional query words: XL98 vbe user form

Keywords: kbdtacode kbhowto kbprogramming KB182411

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.