initializing multiple comboboxes

[natanz]

i am trying to initialize all the comboboxes in a userform by iterating
through them and setting their .list and their .listindex. values.

here is my code:

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim WT As Range
Dim DT As Range

WT = Sheets("sheet2").Range("W_T")
DT = Sheets("sheet2").Range("D_T")

Me.Caption = ActiveCell.Value
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ctl.List = WT.Value
ctl.ListIndex = 0
End If
Next ctl
ComboBox7.List = DT.Value
ComboBox7.ListIndex = 0

TextBox1.Value = 0
End Sub


I am getting an error that says "object variable or with block variable
not set" on the line of code (elsewhere) that displays the userform
"VBA.UserForms.Add(UnitType).Show" This line has worked in the up till
now, so i am pretty sure that the problem is in the code above, and i
am pretty sure it is in the two lines that begin "ctl.list" and
"ctl.listindex". I was hoping and guessing that this syntax would
work, but it looks like i am wrong.

any suggestions?

 

[Bob Phillips]

Try this

Dim ctl As Control
Dim WT
Dim DT

WT = Sheets("sheet2").Range("W_T")
DT = Sheets("sheet2").Range("D_T")

Me.Caption = ActiveCell.Value
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ctl.List = WT
ctl.ListIndex = 0
End If
Next ctl
ComboBox7.List = DT
ComboBox7.ListIndex = 0

TextBox1.Value = 0

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[natanz]

thanks but that is not working. It seems like i need to use a "set"
statement somewhere. but i am not sure where. I am self taught at
this and there are apparently some major gaps.

advice?

 

[Carlos]

Hi

the ranges are not set

Try

Set WT = Sheets("sheet2").Range("W_T")
Set DT = Sheets("sheet2").Range("D_T")

carlos

 

[Bob Phillips]

No, because you need to load an array, not a range variable.

I tested it, it works.

--

HTH

RP
(remove nothere from the email address if mailing direct)