initializing multiple comboboxes
 

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?

initializing multiple comboboxes
 

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" wrote in message
ups.com...
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?

initializing multiple comboboxes
 

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?

initializing multiple comboboxes
 

Hi

the ranges are not set

Try

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

carlos

"natanz" wrote in message
ups.com...
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?

initializing multiple comboboxes
 

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)


"natanz" wrote in message
oups.com...
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?