Using enumeration with controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have some code here in a for next loop.
I want to build a string that uses the enumeration to take calues from 9 rows
of three comboboxes: M1, D1 and Y1 (Month, Day and Year)
Then I want it to pull from M2, D2 and Y2

Unfortunately its printing the actual string "Me.Combobox1.Value" into the cell
ranges as opposed to the value in the comboboxes.

This is what the actual cells look like:

Me.M1.value Me.D1.value Me.Y1.value
Me.M2.value Me.D2.value Me.Y2.value
Me.M3.value Me.D3.value Me.Y3.value
Me.M4.value Me.D4.value Me.Y4.value


Anyone know a way around this? It could really come in handy:


Dim i As Integer
Dim S1 As String, S2 As String, S3 As String

For i = 1 To 9

S1 = "Me.M" & i & ".value"
S2 = "Me.D" & i & ".value"
S3 = "Me.Y" & i & ".value"
MonthCol = Sheets(1).Range("B1000").End(xlUp).Row + 1
DayCol = Sheets(1).Range("C1000").End(xlUp).Row + 1
YearCol = Sheets(1).Range("D1000").End(xlUp).Row + 1

Cells(MonthCol, 2).Value = S1 'Me.M1.Value
Cells(DayCol, 3).Value = S2 'Me.D1.Value
Cells(YearCol, 4).Value = S3 'Me.Y1.Value

Next i

Jim Stiene
 
Something like this should do the trick

Dim ctl as control

For I is 1 to 9
set ctl = me.controls("m"& I)
s1 = Me.ctl
set ctl = me.controls("d"& I)
s2 = Me.ctl
set ctl = me.controls("y"& I)
s1 = Me.ctl

MonthCol = Sheets(1).Range("B1000").End(xlUp).Row + 1
DayCol = Sheets(1).Range("C1000").End(xlUp).Row + 1
YearCol = Sheets(1).Range("D1000").End(xlUp).Row + 1

Cells(MonthCol, 2).Value = S1 'Me.M1.Value
Cells(DayCol, 3).Value = S2 'Me.D1.Value
Cells(YearCol, 4).Value = S3 'Me.Y1.Value

Loop

regards
KM
-----Original Message-----
I have some code here in a for next loop.
I want to build a string that uses the enumeration to take values from 9 rows
of three comboboxes: M1, D1 and Y1 (Month, Day and Year)
Then I want it to pull from M2, D2 and Y2

Unfortunately its printing the actual
Click to expand...
string "Me.Combobox1.Value" into the cell
 
Back
Top