CODEWITHSUNDEEP
excelvba
Álvaro García
Álvaro García

Reputation: 19356

Is it possible to know the cell of the data of selected item in an active combobox?

I want to use an ActiveX combobox in an excel sheet.

To populate the combobox, I use a range of cells with the items to so.

When I select an item in the combobox, I would like to know the cell that correspond to the selected item.

For example, if I have in cell J12 the value "Verdadero" and in the cell J13 the value "False", if I select the intem Verdadero, I would like to know in code that the cell is J12.

How can I achieve that in Excel VBA ?

Upvotes: 2

Views: 43

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can use the ListIndex property of the ComboBox and use that value to look to Offset the range given by the ListFillRange property of the ComboBox.

In the event handler for that combobox (e.g. on Sheet1):

Option Explicit

Private Sub ComboBox1_Change()

    Dim strSelected As String
    Dim lngIndex As Long
    Dim rngList As Range
    Dim rngSourceCell As Range
    Dim strSourceCell As String
    
    strSelected = Me.ComboBox1.SelText
    lngIndex = Me.ComboBox1.ListIndex                           ' <-- the 0-based index of the selection
    Set rngList = Application.Range(Me.ComboBox1.ListFillRange) ' <-- the range you attached to the ComboBox
    Set rngSourceCell = rngList.Cells(1, 1).Offset(lngIndex, 0) ' <-- the cell holding the selected value
    strSourceCell = rngSourceCell.Address(False, False, xlA1, True, False) ' <-- address of that cell
    
    MsgBox strSelected & " from cell " & strSourceCell
    
End Sub

enter image description here

Upvotes: 2

Related Questions