Why ListBox doesn't have a FindString method in Excel-VBA?

Question

Trying to search on a ListBox. Specifically, I want to look at an array of items from the Cell, and for each one that matches an entry in the ListBox, I want it to select that List. I copy-pasted some code that was supposed to let me find a string, but it keeps telling me:

Compile Error: Method or Data Member not found.

Any suggestions?

Relevant Code:

Public local_Target As Range
    ' local_Target is assigned in the sheet function to pass it here

Private Sub Network_ListBox_Enter()
    ' Get data in cell (if any)
    Dim current_data As String
    Dim entries() As String

    current_data = local_Target.Value

    If current_data = "" Then
        Exit Sub
    Else
        entries = Split(current_data, vbNewLine)
    End If

    For Each Item In entries
        FindMyString Item
    Next Item

End Sub


Private Sub UserForm_Terminate()
    Dim index As Integer
    Dim result As String

    ' Iterate through the listbox and create the result, then assign to
    ' Target.value
    For index = 0 To Network_ListBox.ListCount - 1
        If Network_ListBox.Selected(index) Then
            ' stuff
            If result = "" Then
                result = Network_ListBox.List(index)
            ' ElseIf index = Network_ListBox.ListCount - 1 Then
            '     result = result + Network_ListBox.List(index)
            Else
                result = result + vbNewLine + Network_ListBox.List(index)
            End If
        End If
    Next index

    local_Target.Value = result
End Sub

Sub FindMyString(ByVal searchString As String)
   ' Ensure we have a proper string to search for.
   If searchString <> "" Then
      ' Find the item in the list and store the index to the item.
      Dim index As Integer

      index = Me.Network_ListBox.FindString(searchString)
      ' Determine if a valid index is returned. Select the item if it is valid.
      If index <> -1 Then
         Network_ListBox.SetSelected index, True
      'Else
      '   MessageBox.Show ("The search string did not match any items in the ListBox")
      End If
   End If
End Sub

lockedscope · Accepted Answer · 2020-02-06 18:20:26Z

1

I checked Intellisense and I don't think that Method is supported in VBA.
Other documentations I've found refers to .Net Framework only as well.
So maybe, it is not really supported in VBA, but regardless, you can create a function to do just that. Something like below.

Private Function SearchString(mysearch As String, mylist As Variant) As Long
    Dim itm As Variant, idx As Long: idx = 0
    If IsArray(mylist) Then
        For Each itm In mylist
            If mysearch = itm Then
                SearchString = idx: Exit Function
            End If
            idx = idx + 1
        Next
    End If
    SearchString = -1
End Function

And you can use it like this:

Private Sub CommandButton1_Click()
    Dim i As Long
    'do the search
    i = SearchString("WhatImSearching", Me.ListBox1.List)
    'select the item that match your search
    If i <> -1 Then Me.ListBox1.Selected(i) = True 
End Sub

I'm not saying that the function I created above is the most efficient way.
That is just an example to give you an idea for a workaround. HTH.

Important: This works in single column ListBox which have a 1D array list. If you need to work on multi-column ListBox, you'll have to tweak the function a little.

edited Feb 6, 2020 at 18:20

lockedscope

98318 silver badges47 bronze badges

answered Mar 11, 2015 at 23:35

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JSM Over a year ago

Thanks, this did the trick. I probably would've come up with something similar on my own given enough time. As for the intellisense, I trust it 99% of the time; it's that 1% that really gets you.

Collectives™ on Stack Overflow

Why ListBox doesn't have a FindString method in Excel-VBA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related