Defaulting to a particular row in an Excel listbox

Question

I have a working macro that needs an enhancement. Part of the Macro populates a listbox in an Excel UserForm. The code is below:

Dim objExcelApp             As Object
Dim objExcelWorkBook        As Object
Dim objExcelWorksheet       As Object
Dim varTest                 As Variant

lngLastLitRow = objExcelApp.Sheets(1).UsedRange.Rows.Count
Dim strArray() As Variant
ReDim strArray(lngFilteredRowCount - 1, 2)

'Load strArray
With objExcelWorkBook.Sheets(1)
    'The ListBox dimention starts with (0), so set lngListBoxRow
    lngListBoxRow = 0

    'Loop through all the rows in rngLiturgies
    For Each rngCurrentLitRow In rngLiturgies.Rows
        'Populate lngExcelRow
        lngExcelRow = rngCurrentLitRow.Row
        'Select only the rows that are NOT filtered
        If rngCurrentLitRow.EntireRow.Hidden = False Then

        'Fill the array row (lngListBoxRow) with Excel data (lngExcelRow)
        'The Array's column 0 equates to Excel's column 1
        strArray(lngListBoxRow, 0) = objExcelWorkBook.Sheets(1).Cells(lngExcelRow, 1)
        strArray(lngListBoxRow, 1) = objExcelWorkBook.Sheets(1).Cells(lngExcelRow, 2)
        strArray(lngListBoxRow, 2) = objExcelWorkBook.Sheets(1).Cells(lngExcelRow, 3)

        'Increment lngListBoxRow "manually" since it doesn't correspond to
        'the Excel row in the FOR loop
        lngListBoxRow = lngListBoxRow + 1

        Else
        End If

    Next rngCurrentLitRow

End With

'Load ListBoxLit with strArray
With ListBoxLit
    .ColumnCount = 3
    .List = strArray
End With

As an enhancement, I'd like to default the row in the listbox whenever a value in another form field changes. Based on that field change, I'll know the value of three variables that correspond to the elements in the array. How do I select the row in the listbox, when I have those three values available? Thanks for looking at this.

Answer 1

The Selected method of the MSForms.ListBox class takes a single argument index which is the position within the list.

Sub SelectItemInListBox(val$, lBox as MSForms.ListBox)
    Dim i as Long
    For i = 0 to lBox.ListCount - 1
        If LBox.List(i) = val Then LBox.Selected(i) = True
    Next
End Sub

You can call a function like this, and pass the value you want to select, and the listbox object itself, e.g.:

Call SelectItemInListBox("some value", ListBoxLit)

If you have three values you can just call this function three times.