R
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.
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.