XL97: Using the New Keyword to Create Instances of a UserForm
ID: Q167180
|
The information in this article applies to:
-
Microsoft Excel 97 for Windows
SUMMARY
In Microsoft Excel 97, you can use the New keyword to create multiple
instances of a particular class. This article includes sample Visual Basic
for Applications code that uses the New keyword to create multiple
instances of a UserForm. The new instances of the UserForm are copies of
the original UserForm and can be controlled independently of the original
UserForm.
MORE INFORMATION
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
the Microsoft fee-based consulting line at (800) 936-5200. For more
information about the support options available from Microsoft, please see
the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp Creating the Sample UserForm
To create the sample UserForm, do the following:
- Close all open workbooks, and then create a new workbook.
- Start the Visual Basic Editor (press ALT+F11).
- On the Insert menu, click UserForm.
- Add a ListBox control to the lower-left portion of the UserForm.
- Add a Label control above the ListBox control and change the Caption
property to Click an item.
- Add three CommandButton controls to the right of the ListBox control.
- 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, do the following:
- On the Insert menu, click Module.
- 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
- Double-click the top command button and enter the following code for
the Click event for 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
- Double-click the middle command button and enter the following code for
the Click event for the cmdFormCaption command button:
Private Sub cmdFormCaption_click()
' Display the Caption of the current UserForm.
MsgBox Me.Caption
End Sub
- Double-click the bottom command button and enter the following code for
the Click event for the cmdClose command button:
Private Sub cmdClose_Click()
' Hide the current instance of the UserForm.
Me.Hide
End Sub
- Double-click the ListBox control and enter 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
- Enter the following code for the QueryClose event for 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 example macros, do the following:
- Run the New_UserForms macro.
This step displays the UserForm.
- Click the Create New Instance command button.
Each time you click the Create New Instance command button, a new
instance of the UserForm is created, and a new item is added to the list
box.
- Click any number in the list box.
The relevant instance of the UserForm is displayed.
- Click the UserForm Caption command button.
A message box displays the caption for the currently visible UserForm.
- Click the Hide Form command button.
The currently visible UserForm is hidden, and UserForm1 is displayed.
NOTE: The caption on the bottom command button was programmatically
changed to Hide Form to differentiate it from the bottom button on
UserForm1. When 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.
- If UserForm1 is active, click the Close Form button to stop the macro.
REFERENCES
For more information about using the New keyword, click the Office
Assistant in the Visual Basic Editor, type dim, click Search, and then click to view the "Dim Statement" topic.
NOTE: If the Assistant is hidden, click the Office Assistant button on the
Standard toolbar. If Microsoft Excel Help is not installed on your
computer, please see the following article in the Microsoft Knowledge Base:
Q120802 Office: How to Add/Remove a Single Office Program or Component
Additional query words:
XL97
Keywords : kbprg kbdta KbVBA kbhowto
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbinfo
|