How to get the index of a combo box in Excel spreadsheet by using VBA?

Question

Hi,

I have an Excel 2003 file. In the first sheet, I put a combo box. After that, I assign a macro to handle the change event. The macro is located a module in the VB Editor's Project Explorer box. In the macro, I want to write some lines to get the index of the combo box's selected item index. What should I write?

I wrote the line

If ActiveSheet.cbFilter.Index = 1 Then

but it kept raising error (the name of the combo box is "cbFilter"). The error message is "Object doesn't support this property of method". Please help me.

Many thanks in advance,

Haris

Answer 1

have you added items to your combo list?

try these pieces of code in your debugger: first call Preload() once, then select anything from the combo, then run Readout() .... this should give you a hint.

Sub Preload()
   ActiveSheet.ComboBox1.AddItem "111"
   ActiveSheet.ComboBox1.AddItem "222"
   ActiveSheet.ComboBox1.AddItem "333"
End Sub

Sub ReadOut()
    ActiveSheet.[A1] = ActiveSheet.ComboBox1.ListIndex
    ActiveSheet.[A2] = ActiveSheet.ComboBox1
End Sub

also you should check that you have created a reference to MSForms 2.0 Object library (FM20.DLL - or similar)

EDIT:

I just tested the case of empty Combo ... index will be -1