Using enumeration with controls
 

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

Using enumeration with controls
 

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



.

Using enumeration with controls
 

Thanks. I'll try that