Microsoft Office Tutorials and References
In Depth Information
Creating Collections
Your entire coding process relating to the class module is complete, and it is quite a bit shorter than
all the code you’d have amassed if you coded the KeyPress event for every TextBox! If you were to
open the UserForm and attempt a non-numeric character in any of the 12 TextBoxes, that character
would be disallowed and the Message Box would appear, looking like Figure 21-5.
figurE 21-4
figurE 21-5
crEATing collEcTions
In the preceding example, you created a class for 12 TextBoxes. You knew ahead of time the number
of TextBoxes was 12 because there was a TextBox for each of the 12 calendar months. The question
becomes, what do you do if the count of inclusive TextBoxes is not known? What if your project is
so wide in scope that TextBoxes are being frequently added and subtracted from the UserForm, and
you don’t want to keep modifying the code with every change in TextBox count?
The answer is, you create a collection of TextBoxes by looping through all the controls in the
UserForm. Then, when a TextBox is encountered in the loop, it is automatically added to the
collection, which is then transferred to the class object. Assuming the event code you placed in the class
module has not changed, all that needs to be adjusted is the code in the UserForm module using the
previous example. The first item of business is to prepare a declaration statement at the top of the
module that does not specify a count of TextBox names, such as the following example:
Dim TxtGroup() As New Class1
Next, the following code in the UserForm’s Initialize event will wrap up all the TextBoxes into
one array package using the ReDim Preserve keywords. This method does not depend on how many
TextBoxes are embedded on Sheet1; it simply collects all the ones into the TxtGroup object that it finds.
Private Sub UserForm_Initialize()
Dim intCounterTextBox As Integer, ctl As Control
intCounterTextBox = 0
For Each ctl In Controls
If TypeName(ctl) = “TextBox” Then
intCounterTextBox = intCounterTextBox + 1
Search JabSto ::




Custom Search