View Full Version : ComboBox .List problem

08-08-2007, 11:45 AM
I'm using VB6 to create Excel files and add .bas files to it. From VB6, I'm
running a subroutine (macro) in the Excel file (it belongs to a .bas file I
just added) that adds an Excel Forms combobox to a sheet and sets its source
to a named range on another sheet. Here's the code snippet I'm running:
Set CB = WS.DropDowns.Add(Left, Top, 128.25, 15.75) '(Left, Top, Width,
With CB
.Name = Name
.ListFillRange = Source
.ListIndex = 0
.DropDownLines = 8
.Display3DShading = False
.OnAction = Name & "_Change"
End With

This all appears to be working (except the .ListIndex is not actually
setting the text of the combobox to the first value - this is not my real
Next, I'm running the Change event of the combobox I just added (I also
added the Change event subroutine through code, in case that matters). In it
I have the following line:
SelectedValue =
When I'm in VB6 running this as a macro, the above line errors off saying
"Unable to get the List property of the DropDown class". However, if I save
the Excel file and open it, then this line of code works perfectly.
One other note is that the second part of the line is working from VB6. In SelectedValue = WS.DropDowns("cmbServices").List(WS.DropDowns("cmbServices").ListIndex)The WS.DropDowns("cmbServices").ListIndex does contain a value of zero. So, it does see the object.Thx.

08-14-2007, 06:06 AM
Hi Craig,

Sorry for the long delay in responding to your post!

The DropDowns collection has been replaced with ActiveX Shapes, you should be able to change your block to:

Dim CB As OLEObject
Set CB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=74.25, Top:=75, Width:=108.75, Height:=30)
With CB
.Name = Name
.ListFillRange = Source
.Object.ListIndex = 0 ' .ListIndex = 0
.Object.ListRows = 8 ' .DropDownLines = 8
' .Display3DShading = False
End With

Then for your selected value you could use SelectedValue = CB.Object.List(CB.Object.ListIndex)