Is it possible to assign a variable range to an Embeded Control Toolbox
ComboBox? If so, how? Here's the code I've been trying to get to work.

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As OLEObject
Dim AllCT As Range
Dim i As Integer

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.OLEObjects("ComboBox1")

For i = 2 To 20
If IMDS.Range("AA" & i).Value = "" Then GoTo Line1
Next i
Line1:
Set AllCT = IMDS.Range("AA2:AA" & i - 1)
CB1.ListFillRange = AllCT
End Sub

List fill range takes a string not a range object. That being said give this
a look...

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As ComboBox
Dim AllCT As Range

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.ComboBox1

With IMDS
Set AllCT = .Range(.Range("AA2"), .Range("AA2").End(xlDown))
End With
CB1.ListFillRange = AllCT.Address
End Sub
--
HTH...

Jim Thomlinson


"JayWes" wrote:

Is it possible to assign a variable range to an Embeded Control Toolbox
ComboBox? If so, how? Here's the code I've been trying to get to work.

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As OLEObject
Dim AllCT As Range
Dim i As Integer

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.OLEObjects("ComboBox1")

For i = 2 To 20
If IMDS.Range("AA" & i).Value = "" Then GoTo Line1
Next i
Line1:
Set AllCT = IMDS.Range("AA2:AA" & i - 1)
CB1.ListFillRange = AllCT
End Sub

If you are dealing with an external workbook you may need to change
Set CB1 = IMDS.ComboBox1
to
Set CB1 = IMDS.OLEObjects("ComboBox1")

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

List fill range takes a string not a range object. That being said give this
a look...

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As ComboBox
Dim AllCT As Range

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.ComboBox1

With IMDS
Set AllCT = .Range(.Range("AA2"), .Range("AA2").End(xlDown))
End With
CB1.ListFillRange = AllCT.Address
End Sub
--
HTH...

Jim Thomlinson


"JayWes" wrote:

Is it possible to assign a variable range to an Embeded Control Toolbox
ComboBox? If so, how? Here's the code I've been trying to get to work.

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As OLEObject
Dim AllCT As Range
Dim i As Integer

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.OLEObjects("ComboBox1")

For i = 2 To 20
If IMDS.Range("AA" & i).Value = "" Then GoTo Line1
Next i
Line1:
Set AllCT = IMDS.Range("AA2:AA" & i - 1)
CB1.ListFillRange = AllCT
End Sub

Jim,

Thanks for the response, but the code gets hung up he

Set CB1 = IMDS.ComboBox1

I get a message box saying, "Method or data member not found"

Any ideas why?


"Jim Thomlinson" wrote:

List fill range takes a string not a range object. That being said give this
a look...

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As ComboBox
Dim AllCT As Range

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.ComboBox1

With IMDS
Set AllCT = .Range(.Range("AA2"), .Range("AA2").End(xlDown))
End With
CB1.ListFillRange = AllCT.Address
End Sub
--
HTH...

Jim Thomlinson


"JayWes" wrote:

Is it possible to assign a variable range to an Embeded Control Toolbox
ComboBox? If so, how? Here's the code I've been trying to get to work.

Private Sub Workbook_Open()
Dim IMDS As Worksheet
Dim CB1 As OLEObject
Dim AllCT As Range
Dim i As Integer

Set IMDS = Workbooks("IMDS Calc.xls").Worksheets("IMDS")
Set CB1 = IMDS.OLEObjects("ComboBox1")

For i = 2 To 20
If IMDS.Range("AA" & i).Value = "" Then GoTo Line1
Next i
Line1:
Set AllCT = IMDS.Range("AA2:AA" & i - 1)
CB1.ListFillRange = AllCT
End Sub