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