Excel ComboBox - Setting Value

[CraigR]

I'm having trouble setting the display value of a combobox on a worksheet from code. This is not the ActiveX ComboBox, but the one built into the Excel Forms objects. Here's what I have:

Dim CB as Object

Set CB = WS.DropDowns.Add(Left, Top, 128.25, 15.75)
With CB
.Name = Name
.ListFillRange = Source
.DropDownLines = 8
.Display3DShading = False
.OnAction = Name & "_Change"
End With
CB.ListIndex = 0
I thought setting the ListIndex would force the text, but it does not.

I found a reference telling me the following format to retrieve the text:
SelectedValue = WS.DropDowns("cmbServices").List(WS.DropDowns("cmbServices").ListIndex)

This works from the Combobox's Change event, but it doesn't seem to work when I just add the combobox on the fly using the above code. It balks at the .List property saying it can't be found.
Any suggestions?
Thx.
Craig

[Bob Phillips]

Are you sure that you are actually loading the DD? Listfillrange should be a string cell address, not an array of values. If you want the array, use List.

[rory]

The ListIndex starts at 1 so if you set it to 0 you will get a blank. It will also fail if you try and access List(0) because List(1) is the first item.

[CraigR]

Setting the ListIndex to 1 instead of zero was my entire problem. I thought the .List was an array, so it would be zero based. I should have asked you guys first, it would have saved me about 2 days looking into why .List was not available from the VB6 code I'm using to create this and yet it worked fine inside Excel (once I had selected a value in the combobox, so the combobox's .ListIndex was no longer set to zero when VB6 tried to use it)!!!!
Some of the biggest pains are solved with the simplest solutions.
Thx.
Craig

[rory]

It would be more useful if it gave a Subscript Out of Range error, rather than a failure to get the List property, wouldn't it! :)