ranges in combobox

[natanz]

i am trying to use a range to fill a series of comboboxes. I am using
a for loop to iterate through the comboboxes, and set the range as the
list in the combobox. the problem is that i want to update to the
range in response to a property specific to the combobox (.tag).

here's my code

Public Sub init_cboxes(ByVal LotLoc As String, ByVal MyForm As Object)
Dim ctl As Control

WT = Range("W_T")
DT = Range("D_T")

MyForm.Caption = LotLoc & ActiveCell.Value
For Each ctl In MyForm.Controls
If TypeName(ctl) = "ComboBox" Then
RangeFill (ctl.Tag)
ctl.List = WT
ctl.ListIndex = 0
End If
Next ctl

MyForm.TextBox1.Value = 0
End Sub

and here's my code for the sub Rangefill:

Public Sub RangeFill(wtype As Integer)
Dim i As Integer
For i = 1 To 5
Range("w_t").Cells(2 + (2 * i)) = Price(wtype, i + 2)
Next
End Sub

what i think is happening is that the variable WT is not being updated,
even while the cells of the range are. I tried moving the assignment
line WT = Range("W_T") after the line RangeFill(ctl.tag) but that
didn't work. Any Advice.

 

[Guest]

Hi

Are you sure you want to set the list property and not the row source? The
list property is usually used with an array. There are several ways to
populate the data but here's an example using the row sourceproperty of the
combo box. This will link the combo box to a range of cells on a sheet.

Me.ComboBox1.RowSource = Sheet1.Range("A1:A5").Address

If this doesn't help, try going into a little more detail of how you want to
populate the combo boxes. As in do you want the combo boxes linked to the
cells or do you want the list held independently in the combobox once
populated?

Barry

 

[Tom Ogilvy]

Public Sub init_cboxes(ByVal LotLoc As String, ByVal MyForm As Object)
Dim ctl As Control
DT = Range("D_T")

MyForm.Caption = LotLoc & ActiveCell.Value
For Each ctl In MyForm.Controls
If TypeOf ctl is MSForms.Combobox Then
RangeFill ctl.Tag
ctl.List = Range("W_T").Value
ctl.ListIndex = 0
End If
Next ctl

MyForm.TextBox1.Value = 0
End Sub

Should work. If it doesn't, then I would look at what RangeFill is doing
and make sure it is correctly updating the W_T range