Microsoft Office Tutorials and References
In Depth Information
An Important Benefit of Class Modules
If you insert a class module instead, you can define an object that would be a group of 12 TextBoxes.
You can name your group object TxtGroup and declare it as a TextBox type variable. There is nothing
special about the variable name TxtGroup . I chose it because the idea is to group TextBoxes, but
whatever object variable name makes sense to you will work just as well.
The following VBA declaration statement is a common example that gets placed at the top of your
class module. It defines the class object, and includes the WithEvents keyword, which exposes the
events associated with TextBoxes:
Public WithEvents TxtGroup As MSForms.TextBox
Now that you have defined the TxtGroup variable as a TextBox type object, you can invoke it to
handle the same KeyPress event that you might have written individually for all 12 TextBoxes. As
shown in the following code, you now use the TxtGroup object to have VBA recognize the KeyPress
event triggered by keyboard data entry upon any one of the 12 TextBoxes in your TxtGroup object.
The code to handle an event for all 12 TextBoxes is the same for TxtGroup as it is for TextBox1,
except for the name of the object.
Private Sub TxtGroup_KeyPress _
(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 ‘numbers 0-9
KeyAscii = 0
MsgBox “You entered a non-numeric character.”, _
“Numbers only please!”
Keep in mind that, so far, all you have done is define the object, but it still exists only as a concept.
The next step is to create your defined object (formally known as instantiating it) to make it a
working object that responds to events, and becomes associated with methods and properties. At this
moment, with the UserForm created and the class module selected with the preceding code in it,
your work in the class module is complete. Your VBE window will look similar to Figure 21-4.
The final step is to go into the UserForm module and instantiate the TxtGroup object that will be a
group of 12 TextBoxes. At the top of the UserForm module, declare a variable for 12 TextBoxes to
instantiate the TxtGroup class object, with the New keyword for the Class1 module name:
Dim txtBoxes(1 To 12) As New Class1
Using the Initialize event, declare an Integer type variable that will assist in looping through the
12 TextBoxes. Set each TextBox as a member of the TxtGroup class.
Private Sub UserForm_Initialize()
Dim intCounterTextBox As Integer
For intCounterTextBox = 1 To 12
Set txtBoxes(intCounterTextBox).TxtGroup = _
Controls(“TextBox” & intCounterTextBox)