control arrays (collection objects?) in VBA

[davers5]

This morning I learned from Juan Pablo G. that you cannot create a control array in VBA like you can in VB. However, there must be a way to do it. For instance, let's say I want 10 text boxes called txtTextBox(0) through txtTextBox (9). Is there a way to do this? I know there must be but I haven't been able to figure it out. Anybody out there know?

Dave

[Russell Hauf]

There is. Are you creating these at run-time, or are you wanting to put existing controls into an array?

[davers5]

Can you create them at design time? If not, I'd want to create them at run time. Then again, I'd be interested to know how you'd put existing controls in an array, too.

[Mark O'Brien]

I guess if you're going to do it, either at run time or design time, you're going to have to create your own "collection" object to hold the control array. You'll probably have two a class modules as well. One to define the the control and assign handle the events and the other the handle the collection. i.e. add/remove controls etc etc

[Damon Ostrander]

Hi David,

Yes, you can create an array of TextBoxes. But you didn't mention what type of textbox you were referring to, so I will assume that you meant the ActiveX TextBox control that is on the Controls toolbar for use on userforms. There are also TextBox drawing objects (actually Shape objects), and these can be put into arrays using the same approach.

Here's an example:

Dim TBarray(1 To 10) As Control
Set TBarray(1) = TextBox1
Set TBarray(2) = TextBox2
etc.

If the textboxes are all named TextBoxN (the default naming) then you could use a loop to assign all the textboxes to the array:

Dim TBarray(1 To 10) As Control
Dim i As Integer
For i = 1 To 10
Set TBarray(i) = Controls("TextBox" & i)
Next i

And of course you can use the elements of the TBarray as if they were the TextBox. For example, to set the text of the third textbox:

TBarray(3).Text = "Your Name"

[Russell Hauf]

I guess I should have asked what you want to do with the array. I also think I spoke too soon because this is very difficult.

It involves creating a dynamic array that gets filled when the form loads, and also a Class module. Basically you loop through controls of the form as it opens (initialize event), setting each control of that type (the type you desire) to your array.

I suggest John Walkenbach's Power Programming book. Sorry if I got your hopes up by answering your post so fast and then left you out to dry.

-rh

[davers5]

Hello all!

When Juan Pablo G. informed me that you couldn't create a control array in the same way you did in VB I was just interested in finding out how to do it. I'm not actually using it for anything. Now, Damon's code helped me out a lot. So I've just successfully created a text box array on a userform with this code:

Private Sub UserForm_initialize()
Dim TBarray(0 To 5) As Control
Dim i As Integer
Dim intTop As Integer

intTop = 0
For i = 0 To 5
Set TBarray(i) = Controls.Add("Forms.TextBox.1", "TextBox" & i)
TBarray(i).Top = intTop + 20
TBarray(i).Text = "Name: " & TBarray(i).Name
intTop = intTop + 20
Next i

End Sub

The key difference in this code is the Add property of the control function. You have to use the correct control ID as the first argument in order to let VBA know what kind of control to add (see help for a list of ID names). Thanks for the help!

Dave

[kazlady]

Hey guys,
you hit on exactly what I am doing. I have 26 checkboxes at the top of my excel document that when selected check more checkboxes at the bottom of the spreadsheet. Ideally I would use Access fo rthis project, but my employer doesn't train or support Access so it won't be usable by anyone else. I am also new to writing code. Would the array code go into a Private Sub? To this point I have only worked with private subs, 1 for each checkbox on the page but would like to use arrays to reduce the amount of coding needed. All of the checkboxes perform the same function, just using their own properties. i.e: CheckBox1's caption is "Adventure". When I click CheckBox1 it searches the spreadsheet for channels in the adventure pack and activates them. If I use an array, I could have it so it refers back to its own caption to find out what to search for. Liek I said. I have the code working, but I am using a private sub for each of the 26 boxes. Would I put this array into the private sub or is there something else I need to know?