Solved: Looping through controls

[jauner]

Is there a way to loop through specific controls on a form?

What I am specifically asking is this:

I have a form with 10 controls on it that are all named the same except they have a number appended to them. so one in lblquestion1, etc. I want to loop through an array of values and assign entry 1 to lblquestion1, entry 2 to lblquestion2 etc. I was thinking that a control variable could be used but I dont have a lot of experience with that.

Any ideas?

[Norie]

Something like this?


For Each ctl In Me.Controls
MsgBox ctl.Name
Next ctl

[jauner]

Kind of. But I need to reference specific controls that names started with lblquestion and then have a number appended to them. I guess I could probably do an IF check and if it was the correct control name then assign the right array element to it.

[mdmackillop]

Hi Jauner,
The following method lets you cycle through similarly named controls
Regards
MD


Private Sub UserForm_Initialize()
For i = 1 To 3
Me.Controls("Textbox" & i) = i
Next
End Sub

[Norie]

I think that's exactly what you would need to try.

You could use the text functions like Left, Mid etc to do that.

By the way is this the same database that you asked for help with in your 'Advanced Query' post?

If it is, is there no way you can change the data structure?

If you could I'm pretty sure it would make your life, and any other user's life, far easier.

[jauner]

Yes it is the same database, but I have been contracted to make modifications to it and they want to keep the structure the way it is. I have suggested having each question being a detail record but they do not want to go that route.

Anyway I will try the code snippet from mdmackillop and it looks to be what I need.

Thanks all.

[jauner]

It worked great but I have one more question. Can this be done in a general type function so i could pass the form name to it so I do have to repeat this in the four forms I need it on?

Also I need to reference these controls from the main form and the controls are on a subform. How does that impact this?

I apologize for all these questions

[chocobochick]

You can definately pass the form as an argument for a sub or function (though I don't know what you'd return on a function for this):

Private Sub editLabels(frm as Form, maxCount as Integer)
Dim i as Integer
For i = 1 To maxCount
With frm.Controls("lblquestion" & i)
' Do whatever you need to do here
End With
Next
End Sub

If you wanted to cycle through these controls from a form's subform, you could call the procedure like so (edit italic names for your own):

editLabels Forms("NameOfMainForm").NameOfSubformControl.Form, NumberOfLabels

[NYBanker]

Thanks. I just joined and was doing a search and found this post. It does exactly what I need.

Too bad VBA doesn't have the array concept for controls (ie: txtInput(1), txtInput(2) that VB has).

Cheers.