This can't be the best way to do this

[Chris A]

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

 

[Doug Glancy]

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

 

[Tom Ogilvy]

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

 

[Doug Glancy]

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]

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.

 

[Chris A]

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]

Dim ctl as Control

isn't necessary.