Search option listing results in listbox without duplicates and populating values to textboxes and c-CodePudding

I'm new to VBA and what I have so far is mashup from various tutorials and websearches. But so far it works how I want. Now I'd like to add search option and after hours of websearch I cannot find right solution.

Basically I'm trying to create userform which shows data from sheet in listbox, when I double-click item on listbox it shows values of defined cell in textboxes and checkboxes. Got that. I'd like to add search option to list results in same cleared listbox without duplicates (if searched value appears multiple times in same row list it only once in listbox) and when I double-click on an item in listbox it will show details of that record in userform textboxes with checkbox values. All works fine with CheckBox1 changing colours aswell.

What I tried so far. My listbox populates with all data and on double click shows everything properly.

Sub All_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("TABLE")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.ListBox1
     .ColumnHeads = False
     .ColumnCount = 12
     .ColumnWidths = "0,70,60,60,0,0,0,0,0,0,120,0"
     .List = Range(Cells(1, 1), Cells(last_Row, .ColumnCount)).Value
     .RemoveItem 0    
End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
Me.Checkbox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
Me.Checkbox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 9)
Me.CheckBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 10)
Me.CheckBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 11)
End Sub

Private Sub CheckBox1_Change()
If CheckBox1.Value Then
CheckBox1.ForeColor = &H8000&
Else
CheckBox1.ForeColor = &HC0&
End If
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Checkbox values are changing properly from red to green and vice versa. cbgreen cbred

Then I tried to add search option and it kind of works, but checkbox values being greyed out despite showing right true value (but still works with all data listed). cbgrey

Private Sub searchButton_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("TABLE")
Dim i As Long
Dim x As Long
Dim p As Integer, k As Integer

Me.searchTextBox = LCase(Me.searchTextBox)

If Me.searchTextBox = "" Then
Call All_Data
Exit Sub
End If

With Me.ListBox1
     .Clear
     .ColumnHeads = False
     .ColumnCount = 12
     .ColumnWidths = "0,70,60,60,0,,0,0,0,0,120,0"

For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row

For x = 1 To Len(sh.Cells(i, 2))
p = Me.searchTextBox.TextLength
For k = 2 To .ColumnCount - 4

If LCase(Mid(sh.Cells(i, k), x, p)) = Me.searchTextBox And Me.searchTextBox <> "" Then
     .AddItem
     .List(.ListCount - 1, 0) = sh.Cells(i, 1).Value
     .List(.ListCount - 1, 1) = sh.Cells(i, 2).Value
     .List(.ListCount - 1, 2) = sh.Cells(i, 3).Value
     .List(.ListCount - 1, 3) = sh.Cells(i, 4).Value
     .List(.ListCount - 1, 4) = sh.Cells(i, 5).Value
     .List(.ListCount - 1, 5) = sh.Cells(i, 6).Value
     .List(.ListCount - 1, 6) = sh.Cells(i, 7).Value
     .List(.ListCount - 1, 7) = sh.Cells(i, 8).Value
     .List(.ListCount - 1, 8) = sh.Cells(i, 9).Value
     .List(.ListCount - 1, 9) = sh.Cells(i, 10).Value
     .List(.ListCount - 1, 10) = sh.Cells(i, 11).Value
     .List(.ListCount - 1, 11) = sh.Cells(i, 12).Value
End If
Next k
Next x
Next i
RemoveDuplicates Listbox1
End With
End Sub

Sub RemoveDuplicates(aListBox As MSForms.ListBox)
Dim i As Long, j As Long
With aListBox
For i = .ListCount - 1 To 1 Step -1
For j = 0 To i - 1
If (.List(i, 0) = .List(j, 0)) Or (.List(i, 0) = vbNullString) Then
     .RemoveItem i
Exit For
End If
Next j
Next i
End With
End Sub

Is there another way to search, list results in listbox without duplicates and on double-click to populate values to textboxes and ComboBox1 not greyed out (changing colour)?

Thank you for any suggestions and help.

CodePudding user response：

Avoid the duplicates by exiting the column loop at the first match.

Option Explicit
Const COLWIDTHS = "0,70,60,60,0,,0,0,0,0,120,0"

Private Sub searchButton_Click()

    Dim sh As Worksheet, data
    Dim s As String, lastrow As Long
    Dim i As Long, j As Long, k As Long, n As Long
    
    s = Trim(LCase(Me.searchTextBox)) ' search term
    If Len(s) = 0 Then
        Call All_Data
        Exit Sub
    End If
    
    With Me.ListBox1
         .Clear
         .ColumnHeads = False
         .ColumnCount = 12
         .ColumnWidths = COLWIDTHS
    End With
    
    Set sh = ThisWorkbook.Sheets("TABLE")
    With sh
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' use array to speed up search
        data = .Range("A1:L" & lastrow).Value2 ' 12 columns
    End With
    
    'search
    n = 0 ' list index
    s = "*" & s & "*" ' like
    For i = 2 To lastrow
        For j = 2 To 8 ' search col B to H
           If LCase(data(i, j)) Like s Then
               With Me.ListBox1
                    .AddItem
                    For k = 1 To 12 ' Col A to L
                       .List(n, k - 1) = data(i, k)
                    Next
                    n = n   1
               End With
               Exit For
           End If
        Next
    Next
        
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim n As Long, i As Long, c As Control
    With Me.ListBox1
        i = .ListIndex
        If i < 0 Then Exit Sub
        For n = 1 To 7 ' A to G
            Set c = Me.Controls("TextBox" & n)
            c.Value = .List(i, n - 1)
        Next
        Me.ComboBox1.Value = .List(i, 7) ' col H
        For n = 9 To 12 ' col I - L
            Set c = Me.Controls("CheckBox" & n - 8)
            If .List(i, n - 1) = 1 Then
                c.Value = True
                c.ForeColor = &H8000&
            Else
                c.Value = False
                c.ForeColor = &HC0&
            End If
        Next
    End With

End Sub

Sub All_Data()
    Dim sh As Worksheet, last_Row As Long
    Set sh = ThisWorkbook.Sheets("TABLE")
    With sh
        last_Row = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    With Me.ListBox1
         .ColumnHeads = False
         .ColumnCount = 12
         .ColumnWidths = COLWIDTHS
         .List = sh.Range(sh.Cells(2, 1), sh.Cells(last_Row, .ColumnCount)).Value
    End With
End Sub

' repeat for checkboxes 2,3 4
Private Sub CheckBox1_Change()
    If CheckBox1.Value Then
        CheckBox1.ForeColor = &H8000&
    Else
        CheckBox1.ForeColor = &HC0&
    End If
End Sub

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

Private Sub UserForm_Activate()
    All_Data
End Sub