Excel UserForm: Reference Objects Implicitly for a Loop

[Aeneas]

I have a UserForm with six groups of textboxes representing teams. For each team, a textbox holds "Team Name", "Coach's Name", "Number of Players", "Wins", "Losses".

What I want to do is loop through each group, like:


For myTeams = 1 to 6

TextBox(myTeams).Value = "Lakers"
TextBox(myTeams + 1).Value = "Phil Jackson"
TextBox(myTeams + 2).Value = "15"
TextBox(myTeams + 3).Value = "52"
TextBox(myTeams + 4).Value = "30"

Next myTeams



Unfortunately, this doesn't work. Neither does:

UserForm1.TextBoxes("TextBox" & myTeams).Value
UserForm1.Objects("TextBox" & myTeams).Value


What should my syntax be here? I have read some forum stuff here about looping through all collections, but I need to be more specific because each textbox is going to take a different type of value from names to numbers, etc.

For example, I need to do something along the lines of if textbox(team + 3) > 41 then <something> as in if the team has a greater than 50% win percentage (basketball has 82 games I think). This doesn't work for all controls or all textboxes, but always works for textbox(team + 3).

Obviously above my code is going to be more detailed as in I am not setting all six to the same thing, but I wanted to illustrate that I need to reference these differently depending on the situation.

Any help would be appreciated. Thanks!

 

[Loomah]

Hi
I haven't looked too far into this and it's not something I've ever done!

Having said that it sounds like you need a control array which isn't possible in VBA.

Have a look at this FAQ and see if it's any help

faq707-4085

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Zathras]

I would apply the KISS method and simply designate a ControlSource property for each of your text boxes.

By having a single worksheet cell linked to each text box, you can manipulate the worksheet cells any which way you want and the text boxes will track. I would set up a rectangular range that mirrors the layout of the text boxes.

You can use an out-of-the-way area in your main worksheet, or set up a hidden sheet.

Of course, if this is just a programming exercise you can have more fun working with the object collection. But if this is to solve a real-world problem, then the simpler you make it, the easier it will be to maintain.

 

[Aeneas]

The FAQ looks promising, I'm just surprised that one cannot use something like:

Sheets("Sheet1").Select

for:

TextBoxes("TextBox" & Num).Value


or even:

Sheets(16).select

for:

TextBox(Num).Value



I find many applications where it would be easier if I could reference in-VBA controls (such as this one) how I would an Excel object.

But thanks for the suggestions!

Cheers, Tom

 

[Zathras]

If you haven't already done so, check out thread707-831488