Transpose technique not populating ListFillRange of ActiveX combobox

[JimC]

I'm using a Forms combobox to make a selection from a horizontal range
named "Labels".

This code works on the Forms combobox:

' Add combo box 1 for channel 1
ActiveSheet.DropDowns.Add(0, 63.75, 82.5, 15.75).Select
With Selection
.ListFillRange = ""
.List =
Application.WorksheetFunction.Transpose(Range("Labels"))
End With

I'd like to use the richer ActiveX combobox instead to use features
such as MatchEntry.

Naturally, I tried using the same Transpose technique with an ActiveX
combobox but without success:

' create combo box with the Controls toolbar
Dim myCB As OLEObject
Set myCB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.
1", Link:=False, _
DisplayAsIcon:=False, Left:=0, Top:=127.5, Width:=82.5,
Height:=12.75 _
)

myCB.ListFillRange =
Application.WorksheetFunction.Transpose(Range("Labels"))
I can make it work if I set the ListFillRange to a transposed range of
"Labels" but that isn't as satisfying as using the Transpose function.

I'm missing something real basic here but can't figure it out.

Anyone?

 

[Dave Peterson]

..listfillrange needs to have a range.

You used .list with the dropdown from the Forms toolbar. You can use .list with
the combobox from the control toolbox toolbar:

myCB.Object.List = Application.WorksheetFunction.Transpose(Range("Labels"))

 

[JimC]

.listfillrange needs to have a range.  

You used .list with the dropdown from the Forms toolbar.  You can use .list with
the combobox from the control toolbox toolbar:

myCB.Object.List = Application.WorksheetFunction.Transpose(Range("Labels"))















--

Dave Peterson- Hide quoted text -

- Show quoted text -

It worked! Of course, you knew that. Thanks for the help.