CODEWITHSUNDEEP
vbaexcellistbox
JSM
JSM

Reputation: 225

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

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

Upvotes: 1

Views: 4662

Answers (1)

L42
L42

Reputation: 19737

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.

Upvotes: 1

Related Questions