CraigR
08-10-2007, 07:23 AM
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
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