copy userform input to cells

[natanz]

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.

 

[Executor]

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

 

[John]

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

 

[Norman Jones]

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.

 

[natanz]

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.

 

[natanz]

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?

 

[Norman Jones]

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.

 

[natanz]

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.