Getting ComboBox.ListIndex with few copies of elements

user_11914624 0
Viewed 63

I had a ComboBox List with two the same values with the ListFillRange value: Sheet2!A2:A4:

enter image description here

List in Sheet2:

enter image description here

Private Sub ComboBox1_DropButtonClick()

Dim ComboBoxList As String

'Counts how many values in column A, which is '4'
ComboBoxList = Application.Evaluate("COUNTA(Sheet2!A:A)")

'Changes ListFillRange Value into 'Sheet2!A2:A4'
ComboBox1.ListFillRange = "Sheet2!A2:A" + ComboBoxList

Debug.Print ComboBox1.ListIndex

And wanted to print out the 3rd index which is the value should be '2':

Debug.Print ComboBox.ListIndex

But returns '1' instead of '2'

Edit: The list is made out of listfillrange. Not by AddItem

vba excel
1 Answers

The problem is that you have the Debug.Print ComboBox1.ListIndex in the ComboBox1_DropButtonClick. Remove it there and put it into the ComboBox1_Change event and at works properly.

Option Explicit

Private Sub ComboBox1_DropButtonClick()
    Dim ComboBoxList As String
    
    'Counts how many values in column A
    ComboBoxList = Application.Evaluate("COUNTA(Sheet2!A:A)")
    
    'Changes ListFillRange Value into 'Sheet2!A2:A4'
    ComboBox1.ListFillRange = "Sheet2!A2:A" + ComboBoxList
End Sub

Private Sub ComboBox1_Change()
    Debug.Print Me.ComboBox1.ListIndex
End Sub
user_32196130
Related