This can't be the best way to do this

  • Thread starter Thread starter Chris A
  • Start date Start date
C

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