Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: control arrays (collection objects?) in VBA

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    May 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: control arrays (collection objects?) in VBA

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •