Set default value of combo box

[cplindem]

I have added an ActiveX combo box with the following code:


ActiveSheet.OLEObjects.Add "Forms.ComboBox.1"
ActiveSheet.OLEObjects("ComboBox1").Name = "cmbMyCombo"


It works fine. I also have code that populates the combo box. It also works fine, but no matter what I try, the default value of the combo box is blank. I assumed the following code would set the default value. It runs without errors, but seemingly does nothing:


ActiveSheet.OLEObjects("cmbMyCombo").Object.ListIndex = 2

Any ideas?

[jonh]

Works ok here.


Sub test()
With ActiveSheet.OLEObjects.Add("Forms.ComboBox.1").Object
.AddItem "x"
.AddItem "y"
.AddItem "z"
.ListIndex = 1
End With
End Sub

[snb]

sub M_snb()
with ActiveSheet.OLEObjects.Add("Forms.ComboBox.1")
.Name = "cmbMyCombo"
.object.list=[A1:A10].Value
.object.listindex=0
end with
End Sub

[cplindem]

Thanks to both of your examples, I was able to confirm that I could also set the default in this manner, when isolated outside of the rest of my giant code. I thus concluded that something after that was resetting the default to blank. I am also setting the linked cell for this combo box. Apparently, this resets the default value. I'm not sure why. Regardless, simply using the ListIndex method after setting the linked cell solved the problem for me.


With ActiveSheet.OLEObjects("cmbMyCombo")
.LinkedCell = "formulas!B1"
.Object.ListIndex = 0
End With

Much appreciated.

[snb]

That's why you should avoid 'linkedcell' or "rowsource' or 'listfillrange', but use .List instead.

[cplindem]

How would I specify the linked cell with .List?
I just searched around for a bit, and everything I found recommends .LinkedCell.

[snb]

To populate a combobox use: .list
to change a cell use: cell(1,5).value=combobox1.value

[cplindem]

Ah, I see. That's not functionally the same as actually linking the cell. The value in cell(1,5) won't update when the combo box value changes. Yes, I realize I could write an event handler to update it every time, but I actually can't leave any VBA in my final product, so it won't work for my purposes.

Thanks for the assistance.