How to Search in Listbox With Unique Value

[JarekM]

Hi, can someone help me adjust this code so that way only unique values would show up?

Function searchPropertyName(textsearch As String)

    Dim i       As Long
    Dim x       As Boolean
    Dim xvalue  As String

    Me.ListBox1.Clear


    For i = lbl_tipRow.caption To 5501
        xvalue = Cells(i, lbl_tipCol).value
'        If UCase(Left(valor_celula, Len(textsearch))) = UCase(valor_pesq) Then
        If LCase(xvalue) Like "*" & LCase(textsearch) & "*" Then 'LCase(
            If x = False Then
            Me.ListBox1.Visible = True
            x = True
            End If

            If xvalue <> "" Then
            Me.ListBox1.AddItem xvalue
            End If


            If Me.ListBox1.ListCount >= 20 Then
            Me.ListBox1.Height = 160
            Else
            Me.ListBox1.Height = Me.ListBox1.ListCount * 12 + 2 '9.75 + 8  '32
            End If
        End If
    Next
  
    If Me.ListBox1.ListCount <= 0 Then
        Me.ListBox1.Visible = False
    End If



End Function

Thanks for trying to help, I'm new to VBA so I don't know everything.

 

[Fluff]

Try

Function searchPropertyName(textsearch As String)

    Dim i       As Long
    Dim x       As Boolean
    Dim xvalue  As String

    Me.ListBox1.Clear

   With CreateObject("scripting.dictionary")
      .comparemode = 1
      For i = lbl_tipRow.Caption To 5501
         xvalue = Cells(i, lbl_tipCol).Value
         If LCase(xvalue) Like "*" & LCase(textsearch) & "*" Then 'LCase(
            If Not .Exists(xvalue) Then .Add xvalue, Nothing
         End If
      Next i
      If .Count > 0 Then
         Me.ListBox1.Visible = True
         Me.ListBox1.list = .Keys
      End If
      
      If Me.ListBox1.ListCount >= 20 Then
         Me.ListBox1.Height = 160
      Else
         Me.ListBox1.Height = Me.ListBox1.ListCount * 12 + 2 '9.75 + 8  '32
      End If
   End With
End Function

Although it should really be a Sub & not a Function.

 

[JarekM]

Thank you for the reply. Your code works well, although there is one minor problem. The height on the Listbox is not properly adjusted. There is an extra empty line of space in the Listbox. That space disappears when I type something in the textbox or when there are more than 20 things in the listbox.

 

[Fluff]

I didn't make any changes to the height of the listbox, so it must have been like that originally.

 

[JarekM]

I know you didn't do anything to the height, but there is like an item that is invisible or something in the listbox that creates an extra empty line of space when I type something in the textbox. Would you know how to fix this?

 

[Fluff]

You will have to adjust yourself as I do not know what you want, or font/font size you are using.

 

[JarekM]

I use font size 10.
As you can see on the left side, when the textbox is empty and the listbox is populated with the values, there is an extra line of empty space, but when I type something in the textbox the empty line of space disappears.
Do you have any idea how to fix this?

 

[Fluff]

You are talking about textboxes & listboxes, but your image appears to show a combobox.
That said, the extra line will be dependant on how you load it originally.

 

[JarekM]

Actually it is a textbox, the common button was created separately, so as to clear the textbox and show the listbox.