Multiple Controls in a Dynamic UserForm

[Rawce]

Hello All,

This week's stumbling block concerns a form that I've built
programmatically. Let's say it looks like this:

Name1 | Date 1 | | Comments |
Name2 | Date 2 | X | Comments |
Name3 | Date 3 | | Comments |
Name4 | Date 4 | X | Comments |
Name5 | Date 5 | X | Comments |
Name6 | Date 6 | | Comments |

When the user hits Save I want it to cycle through the third column of
ComboBoxes and wherever there's an "X" write the values of the first,
second and fourth columns to cells in a spreadsheet.

When I build the form, I namestamp each ComboBox with an R1C1 format
(in the .Name property), so Name1 in my example will be named CBR1C1,
Date 3 will be CBR3C2, etc.

Currently, I'm cycling through all the controls in the corresponding
frame with a For Each loop to find any that have a .Name ending with
"3" (to indicate the third column) and is populated (i.e. with an "X").

As I go through, I find the "X", use InStr and Mid on its .Name to get
the current row number and feed this back to try and get the
corresponding Name, Date and Comment from columns 1, 2 and 4. I suspect
that this is my problem; as I'm within a loop that is cycling through
each control on a particular frame, I'm locked into the particular
control and can't step out of it.

Anyone know a way of cycling through the third column of ComboBoxes and
when there's an "X" in it read the data from other columns?

FYI - I get an Object Variable Not Set Error. I've tried Set and With
to assign the .Name but with no luck.

Cheers,

Ross.

 

[Rawce]

No worries, have solved it. Just added an extra case to read in the
value of the first column (by looking for the "C1" at the end of the
name) as the loop cycles through, so when it gets to the column with
the "X" in it it has a value already there waiting which is overwritten
if not needed. Pretty obvious when you think about it! Probably the
result of working a 12 hour day with either too much or too little
coffee, not sure which yet.

Thanks for looking anyway,

Ross.