Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Controls in a Dynamic UserForm
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Controls in a Dynamic UserForm
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Combobox in a Userform access multiple dynamic tables | Excel Discussion (Misc queries) | |||
Userform for time entry: multiple controls/single value? | Excel Programming | |||
Dynamic controls | Excel Programming | |||
creating dynamic controls | Excel Programming | |||
Dynamic Label for Controls | Excel Programming |