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