Transpose technique not populating ListFillRange of ActiveX combobox
 

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("Lab els"))
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.Combo Box.
1", Link:=False, _
DisplayAsIcon:=False, Left:=0, Top:=127.5, Width:=82.5,
Height:=12.75 _
)

myCB.ListFillRange =
Application.WorksheetFunction.Transpose(Range("Lab els"))
ERROR:"Run-time error '13: Type mis-match"

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?

Transpose technique not populating ListFillRange of ActiveX combobox
 

..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("Lab els"))



JimC wrote:

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("Lab els"))
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.Combo Box.
1", Link:=False, _
DisplayAsIcon:=False, Left:=0, Top:=127.5, Width:=82.5,
Height:=12.75 _
)

myCB.ListFillRange =
Application.WorksheetFunction.Transpose(Range("Lab els"))
ERROR:"Run-time error '13: Type mis-match"

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

Transpose technique not populating ListFillRange of ActiveXcombobox
 

On Sep 5, 7:12*pm, Dave Peterson wrote:
.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("Lab els"))





JimC wrote:

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("Lab els"))
* * 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.Combo Box.
1", Link:=False, _
* * * * DisplayAsIcon:=False, Left:=0, Top:=127.5, Width:=82.5,
Height:=12.75 _
* * * * )

* * myCB.ListFillRange =
Application.WorksheetFunction.Transpose(Range("Lab els"))
* * * * * ERROR:"Run-time error '13: Type mis-match"

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- Hide quoted text -

- Show quoted text -

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