Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
i am writing a macro that has a series of userforms.
data entered into the first userform determines which userform you see next. i would like to copy the info entered into the comboboxes on that second userform back into my spreadsheet. I know how many comboboxes there are in each userform but i was hoping to generalize this routine, by using a for next loop. I based the following code on something i saw in a textbook: dim ctl as control for each ctl in me.controls if typename(ctl) = "combobox" then _ activecell = me.ctl.value activecell.offset(0,1).select next ctl but this doesn't work. the me.ctl.value isn't recognized. Is there some way i can iterate through these comboboxes w/o naming each one individually. thanks in advance for any help you might be able to provide. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
Hi,
Using for each ctl in me.control you are already inside your form. So this should work: activecell.value = ctl.value Excel 2000 does not give the wanted reply on typename(ctl) so I created this Dim ctr As ComboBox ' For Each ctr In Me.Controls On Local Error Resume Next ActiveCell.Value = ctr.Text ActiveCell.Offset(1, 0).Select Next and this works fine. Hoop that helps |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
Hello Natanz,
You're almost there. Have a go with this (untested): Dim ctl As Control For Each ctl in Me.Controls If ctl.Name = "ComboboxName" Then activecell = ctl.Text End If Next ctl Best regards John "natanz" wrote in message oups.com... i am writing a macro that has a series of userforms. data entered into the first userform determines which userform you see next. i would like to copy the info entered into the comboboxes on that second userform back into my spreadsheet. I know how many comboboxes there are in each userform but i was hoping to generalize this routine, by using a for next loop. I based the following code on something i saw in a textbook: dim ctl as control for each ctl in me.controls if typename(ctl) = "combobox" then _ activecell = me.ctl.value activecell.offset(0,1).select next ctl but this doesn't work. the me.ctl.value isn't recognized. Is there some way i can iterate through these comboboxes w/o naming each one individually. thanks in advance for any help you might be able to provide. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
Hi Natanz,
The following worked for me: '========================= Private Sub CommandButton1_Click() Dim rng As Range Dim ctl As MSForms.Control Dim i As Long Set rng = Sheets("Sheet2").Range("A1") '<<===== CHANGE For Each ctl In Me.Controls If TypeName(ctl) = "ComboBox" Then i = i + 1 With rng.Offset(0, i - 1) .Value = ctl.Value End With End If Next ctl End Sub '<<========================= Note that the code avoids making selections which are rarely necessary, and usually ineeficient. --- Regards, Norman "natanz" wrote in message oups.com... i am writing a macro that has a series of userforms. data entered into the first userform determines which userform you see next. i would like to copy the info entered into the comboboxes on that second userform back into my spreadsheet. I know how many comboboxes there are in each userform but i was hoping to generalize this routine, by using a for next loop. I based the following code on something i saw in a textbook: dim ctl as control for each ctl in me.controls if typename(ctl) = "combobox" then _ activecell = me.ctl.value activecell.offset(0,1).select next ctl but this doesn't work. the me.ctl.value isn't recognized. Is there some way i can iterate through these comboboxes w/o naming each one individually. thanks in advance for any help you might be able to provide. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
thanks for your help, It worked just fine.
i think that the problem getting the wanted reply on typename(ctl) was that it was requiring "ComboBox" not "combobox". now i am on to a new problem. The for loop is iterating through my comboboxes and writing the values back to the spreadsheet, but apparently the order is messed up. Is there a way for me to reset the order. taking a stab in the dark, i would say i have to reset controls.item(indexnumber) to get them in the right order, but i am not sure how or where to do this. this is something different than the index of the combobox, it is its index number within the controls collection. I am guessing that it is a reflection of the order in which the controls were created. anyway thanks again for any help you can provide. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
i asked in a followup question about the order in which the for loop
was working. it seemed like my comboboxes were not in the order i expected, and i was getting bad results. Now upon further testing, i find that the comboboxes are in a fine order, it just seems like the ..value of the first one is getting entered in the last cell, rather than the first. so in my cells, i see the input of combobox2,3,4.....combobox1. Is this normal, is there a way to change this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
Hi Natanz
The iterartion sequence is that of the controls collection index values. If ComboxBox1's value is being returned as the last value, this would suggest that ComboBox1 was added to the Userforms controls after the other comboboxes. --- Regards, Norman "natanz" wrote in message ups.com... i asked in a followup question about the order in which the for loop was working. it seemed like my comboboxes were not in the order i expected, and i was getting bad results. Now upon further testing, i find that the comboboxes are in a fine order, it just seems like the .value of the first one is getting entered in the last cell, rather than the first. so in my cells, i see the input of combobox2,3,4.....combobox1. Is this normal, is there a way to change this? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy userform input to cells
Is there any way to change this index order, or at least see it
somewhere. I have somewhere on the order of 50 userforms that i am trying to develop this macro for, and i don't want to have to create them all anew. Norman Jones wrote: Hi Natanz The iterartion sequence is that of the controls collection index values. If ComboxBox1's value is being returned as the last value, this would suggest that ComboBox1 was added to the Userforms controls after the other comboboxes. --- Regards, Norman "natanz" wrote in message ups.com... i asked in a followup question about the order in which the for loop was working. it seemed like my comboboxes were not in the order i expected, and i was getting bad results. Now upon further testing, i find that the comboboxes are in a fine order, it just seems like the .value of the first one is getting entered in the last cell, rather than the first. so in my cells, i see the input of combobox2,3,4.....combobox1. Is this normal, is there a way to change this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm, with no input | Excel Discussion (Misc queries) | |||
How to copy a row with transposing to a column retaining links to input cells? | Excel Worksheet Functions | |||
Formatiing a input Box in a Userform | Excel Discussion (Misc queries) | |||
Userform Input Into Excel | Excel Programming | |||
Copy cells into another workbook with an input box to select the file | Excel Programming |