View Full Version : Solved: Loop through controls on userform

07-26-2007, 08:13 AM
I am having trouble with a For Each Next loop. In brief, I have a Userform with 27+ combo boxes. Each CB's rowsource is dependent upon the previous CB's selection. For this reason I enable the CB's one at a time as the user progresses through the form. Using CB change events I control the enabling of the next CB and Label and the CB's rowsource.

Problem: A user can get to CB10 and may decide to go back and change CB3. This results in the need to clear CB4 - CB10. Currently, I am using individual lines of code to enable all succeeding CB's, set their values to "", then disable the CB's and their labels. (Muchas lineas de codigo).

I want each change event to include a loop that disables all succeeding CB's. In other words, when CB3 changes CB4 -CB27 will disable...when CB17 changes CB18-CB27 will disable.

I was able to write a For Each Next loop to diable all of the CB's and labels in my initialize sub but I can't seem to get the limited loop written correctly. Can someone give me a nudge in the right direction?



07-26-2007, 08:21 AM
If you have your tab order set up correctly, you could loop through each control and check if the tabindex is greater than the current combo's tabindex - if so, clear and disable it.

Bob Phillips
07-26-2007, 08:37 AM
Set the tag property for each CB, and test that, and leaves some gaps in there in case you add any more later.

You coul also prefix the tags, like CB_005,CB_010, etc so that you can pick those controls out amongst all the others.

07-26-2007, 08:39 AM
Thanks, Rory. Good idea. I will give it a try. I will be back to mark this solved or raise another question within the hour.

07-26-2007, 08:44 AM
Thanks. I have all my tab indices set in order so I will try If typename(ctl)=ComboBox and ctl tabindex>4 (etc). I will have to read up a bit on the tag property, I have never used it yet.


07-26-2007, 09:06 AM
Thank you, both, XLD and Rory. Worked like a charm...a new tool for the shed! This is by far the best VBA forum I have found to date. I will mark this resolved.