XL98: Using the New Keyword to Create Instances of a UserForm

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 professionals 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.

Creating the Sample UserForm

To create the sample UserForm, follow these steps:

1. Close all open workbooks, and create a new workbook.
2. Start the Visual Basic Editor by pressing OPTION+F11.
3. On the Insert menu, click UserForm.
4. Add a list box control to the lower-left portion of the UserForm.
5. Add a label control above the list box control and change the Caption property to Click an item.
6. Add three command button controls to the right of the list box control.
7. Use the following properties for the command buttons:
   
   Top Command Button:

         Property    Value
         -------------------------------
   
         Name        cmdNewForm
         Caption     create new instance
   						

   Middle Command Button:

         Property    Value
         ----------------------------
   
         Name        cmdFormCaption
         Caption     UserForm caption
   						

   Bottom Command Button:

         Property    Value
         ----------------------
   
         Name        cmdClose
         Caption     close form
   						

Entering Macro Code

To create the macro, follow these steps:

1. On the Insert menu, click Module.
2. Enter the following code in the General Declarations section of the new module:

         Option Base 1
         ' Counter for instances of UserForms.
         Public mycount As Integer
   
         ' Array containing UserForm objects.
         Public MyForms() As UserForm1
   
         Sub New_UserForms()
   
            ' Display the UserForm.
            UserForm1.Show
   
         End Sub
   						

3. Double-click the top command button and type the following code for the Click event of the cmdNewForm command button:

         Private Sub cmdNewForm_Click()
   
            mycount = mycount + 1
   
            ' Increase the size of the MyForms array by one.
            ReDim Preserve MyForms(mycount)
   
            ' Create a new instance that is of type UserForm1.
            Set MyForms(mycount) = New UserForm1
   
            ' Set the caption of the UserForm to the instance number.
            MyForms(mycount).Caption = "instance " & mycount
   
            MyForms(mycount).cmdClose.Caption = "hide form"
   
            ' Add new item to the list box.
            UserForm1.ListBox1.AddItem mycount
   
         End Sub
   						

4. Double-click the middle command button and type the following code for the Click event of the cmdFormCaption command button:

         Private Sub cmdFormCaption_click()
   
            ' Display the Caption of the current UserForm.
            MsgBox Me.Caption
   
         End Sub
   						

5. Double-click the bottom command button and type the following code for the Click event of the cmdClose command button:

         Private Sub cmdClose_Click()
   
            ' Hide the current instance of the UserForm.
            Me.Hide
   
         End Sub
   						

6. Double-click the list box control and type the following code for the MouseUp event:

         Private Sub ListBox1_MouseUp _
         (ByVal Button As Integer, ByVal Shift As Integer, _
         ByVal X As Single, ByVal Y As Single)
   
            ' Display the selected instance of the UserForm.
            MyForms(UserForm1.ListBox1.ListIndex + 1).Show
   
         End Sub
   						

7. Type the following code for the QueryClose event of UserForm1:

         Private Sub UserForm_QueryClose _
         (Cancel As Integer, CloseMode As Integer)
   
            On Error Resume Next ' Ignore if no forms exist.
   
            ' Setting the value of Cancel to any integer other than 0 prevents
            ' you from clicking the Close ("X") button to close the UserForm.
            Cancel = 1
   
         End Sub
   						

Running the Macros

To run the sample macros, follow these steps::

1. Run the New_UserForms macro.
   
   This step displays the UserForm.
2. Click Create New Instance.
   
   Each time you click Create New Instance, a new instance of the UserForm is created, and a new item is added to the list box.
3. Click any number in the list box.
   
   The relevant instance of the UserForm is displayed.
4. Click UserForm Caption.
   
   A message box displays the caption for the currently visible UserForm.
5. Click Hide Form.
   
   The currently visible UserForm is hidden, and UserForm1 is displayed.
   
   NOTE: The caption of the bottom command button is programmatically changed to "Hide Form" to differentiate it from the bottom button on UserForm1. If you hide one of the created instances of the UserForm, and UserForm1 is active, the caption on the bottom command button is "Close Form," which is the value set at design time.
6. To stop the macro if UserForm1 is active, click the Close Form button.