This can't be the best way to do this
 

I use a few forms and i'm finding i have to cyle through controls, i've
given it a go and can't seem to find what i need, this works but again, i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA

This can't be the best way to do this
 

Chris,

I think this boils it down for you:

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
For Each ctl In UserForm1.Controls
If TypeOf ctl Is msforms.ComboBox Then 'assuming that
you're doing this with all your comboboxes
Tx = .Value
ctl.AddItem Tx
End If
Next ctl
End If
End With
Next rw
Next col

End Sub


hth,

Doug


"Chris A" wrote in message
s.com...
I use a few forms and i'm finding i have to cyle through controls, i've
given it a go and can't seem to find what i need, this works but again, i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA

This can't be the best way to do this
 

As I read his code, it would be more like this: (every value in every column
didn't go in every combobox)

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
controls("Combobo" & col-60).AddItem .Value
End If
End With
Next rw
Next col

End Sub

--
Regards,
Tom Ogilvy

Doug Glancy wrote in message
...
Chris,

I think this boils it down for you:

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
For Each ctl In UserForm1.Controls
If TypeOf ctl Is msforms.ComboBox Then 'assuming
that
you're doing this with all your comboboxes
Tx = .Value
ctl.AddItem Tx
End If
Next ctl
End If
End With
Next rw
Next col

End Sub


hth,

Doug


"Chris A" wrote in message
s.com...
I use a few forms and i'm finding i have to cyle through controls, i've
given it a go and can't seem to find what i need, this works but again,
i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA

This can't be the best way to do this
 

As I was bicycling across town after submitting my answer, it occurred to me
that I'd misread. Thanks, Tom.

Also, "Combobo" in the code below, should read "ComboBox"

Doug

"Tom Ogilvy" wrote in message
...
As I read his code, it would be more like this: (every value in every
column
didn't go in every combobox)

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
controls("Combobo" & col-60).AddItem .Value
End If
End With
Next rw
Next col

End Sub

--
Regards,
Tom Ogilvy

Doug Glancy wrote in message
...
Chris,

I think this boils it down for you:

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
For Each ctl In UserForm1.Controls
If TypeOf ctl Is msforms.ComboBox Then 'assuming
that
you're doing this with all your comboboxes
Tx = .Value
ctl.AddItem Tx
End If
Next ctl
End If
End With
Next rw
Next col

End Sub


hth,

Doug


"Chris A" wrote in message
s.com...
I use a few forms and i'm finding i have to cyle through controls,
i've
given it a go and can't seem to find what i need, this works but
again,
i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA

This can't be the best way to do this
 

Thanks for the correction.

As Doug said:

controls("Combobo" & col-60).AddItem .Value

should be

controls("Combobox" & col-60).AddItem .Value

Sorry about the typo.

--
Regards,
Tom Ogilvy


"Doug Glancy" wrote in message
...
As I was bicycling across town after submitting my answer, it occurred to
me
that I'd misread. Thanks, Tom.

Also, "Combobo" in the code below, should read "ComboBox"

Doug

"Tom Ogilvy" wrote in message
...
As I read his code, it would be more like this: (every value in every
column
didn't go in every combobox)

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
controls("Combobo" & col-60).AddItem .Value
End If
End With
Next rw
Next col

End Sub

--
Regards,
Tom Ogilvy

Doug Glancy wrote in message
...
Chris,

I think this boils it down for you:

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
For Each ctl In UserForm1.Controls
If TypeOf ctl Is msforms.ComboBox Then
'assuming
that
you're doing this with all your comboboxes
Tx = .Value
ctl.AddItem Tx
End If
Next ctl
End If
End With
Next rw
Next col

End Sub


hth,

Doug


"Chris A" wrote in message
s.com...
I use a few forms and i'm finding i have to cyle through controls,
i've
given it a go and can't seem to find what i need, this works but
again,
i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA

This can't be the best way to do this
 

It's easy when you know how hey!?
I get it now, I was struggling trying to increment the controls. After
looking at this though I can't understand what the 'Dim ctl As Control' line
is for, so i tried it commented out and it worked fine. I spotted the typo
though so i'm guessing i must be getting better at this (at least let me
think so)
Thanks for the help.
Chris A

"Tom Ogilvy" wrote in message
...
Thanks for the correction.

As Doug said:

controls("Combobo" & col-60).AddItem .Value

should be

controls("Combobox" & col-60).AddItem .Value

Sorry about the typo.

--
Regards,
Tom Ogilvy


"Doug Glancy" wrote in message
...
As I was bicycling across town after submitting my answer, it occurred
to
me
that I'd misread. Thanks, Tom.

Also, "Combobo" in the code below, should read "ComboBox"

Doug

"Tom Ogilvy" wrote in message
...
As I read his code, it would be more like this: (every value in every
column
didn't go in every combobox)

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
controls("Combobo" & col-60).AddItem .Value
End If
End With
Next rw
Next col

End Sub

--
Regards,
Tom Ogilvy

Doug Glancy wrote in message
...
Chris,

I think this boils it down for you:

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
For Each ctl In UserForm1.Controls
If TypeOf ctl Is msforms.ComboBox Then
'assuming
that
you're doing this with all your comboboxes
Tx = .Value
ctl.AddItem Tx
End If
Next ctl
End If
End With
Next rw
Next col

End Sub


hth,

Doug


"Chris A" wrote in message
s.com...
I use a few forms and i'm finding i have to cyle through controls,
i've
given it a go and can't seem to find what i need, this works but
again,
i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA

This can't be the best way to do this
 

Dim ctl as Control

isn't necessary.

--
Regards,
Tom Ogilvy


"Chris A" wrote in message
s.com...
It's easy when you know how hey!?
I get it now, I was struggling trying to increment the controls. After
looking at this though I can't understand what the 'Dim ctl As Control'
line
is for, so i tried it commented out and it worked fine. I spotted the typo
though so i'm guessing i must be getting better at this (at least let me
think so)
Thanks for the help.
Chris A

"Tom Ogilvy" wrote in message
...
Thanks for the correction.

As Doug said:

controls("Combobo" & col-60).AddItem .Value

should be

controls("Combobox" & col-60).AddItem .Value

Sorry about the typo.

--
Regards,
Tom Ogilvy


"Doug Glancy" wrote in message
...
As I was bicycling across town after submitting my answer, it occurred
to
me
that I'd misread. Thanks, Tom.

Also, "Combobo" in the code below, should read "ComboBox"

Doug

"Tom Ogilvy" wrote in message
...
As I read his code, it would be more like this: (every value in
every
column
didn't go in every combobox)

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
controls("Combobo" & col-60).AddItem .Value
End If
End With
Next rw
Next col

End Sub

--
Regards,
Tom Ogilvy

Doug Glancy wrote in message
...
Chris,

I think this boils it down for you:

Sub Fill()
Dim Tx As String
Dim rw, col As Long
Dim ctl As Control

For col = 61 To 72
For rw = 25 To 35
With Worksheets("Sheet1").Cells(rw, col)
If .Value < "" Then
For Each ctl In UserForm1.Controls
If TypeOf ctl Is msforms.ComboBox Then
'assuming
that
you're doing this with all your comboboxes
Tx = .Value
ctl.AddItem Tx
End If
Next ctl
End If
End With
Next rw
Next col

End Sub


hth,

Doug


"Chris A" wrote in message
s.com...
I use a few forms and i'm finding i have to cyle through
controls,
i've
given it a go and can't seem to find what i need, this works but
again,
i
have a feeling it's not quite right.

Sub Fill()
Dim Tx As String
Dim rw As Long

For rw = 25 To 35


With Worksheets("Sheet1").Cells(rw, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 62)
If .Value = "" Then
Else
Tx = .Value
ComboBox2.AddItem Tx
End If
End With

With Worksheets("Sheet1").Cells(rw, 63)
If .Value = "" Then
Else
Tx = .Value
ComboBox3.AddItem Tx
End If
End With

' <...snip... This repeats quite a bit,

With Worksheets("Sheet1").Cells(rw, 72)
If .Value = "" Then
Else
Tx = .Value
ComboBox12.AddItem Tx
End If
End With
Next rw

End Sub

Thanks for looking
ChrisA