control arrays (collection objects?) in VBA

davers5

Active Member
Joined
Feb 20, 2002
Messages
255
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
There is. Are you creating these at run-time, or are you wanting to put existing controls into an array?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top