Excel Userform Directory - search button using checkboxes

[Kathy93]

Hi

I'm currently making a supplier directory for work.

I have used vba to make a userform to search, add and edit supplier information.

I'm currently having an issue with the search button which is linked to the checkboxes the idea is to select the material in question, eg. checkbox 1-8, select the command button and list the matchings in the listbox.

My database has 5 sheets, and 13 columns for a sheet.

i have attached what i have made so far - if someone can help with a solution, or improvements to make this better, it would be much appreciated.

Thank you

[mdmackillop]

Rename CommandButton6 to CommandButton1

[Kathy93]

Sorry - i made a new button and hadnt linked it.

the main issue is, is that i need it to renew when selecting a new checkbox (or refresh). at the moment it doesn't do that and makes the search button pointless.

All the results, it brings up is aluminium, regardless of the other materials.


Any solutions?

I linked the command button back to commandbutton1

[mdmackillop]

Private Sub CommandButton1_Click()

Columns(2).Cells.Interior.ColorIndex = xlNone  '@@@@@@@@@ for checking
If ListBox1.ListIndex = -1 Then
    MsgBox "First Select Sheet"
    Exit Sub
End If
ListBox2.Clear
ReDim sn(1 To 8)
For x = 1 To 8
    If Me("CheckBox" & x) Then
        j = j + 1
        sn(j) = x
    End If
Next
qt = 0
With Sheets(ListBox1.Value)
    lr = .Range("B" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
        For x = 3 To lr
            For i = 1 To j
                If .Cells(x, sn(i) + 6) <> "" Then   'Change here  @@@@@@@@@@@
                qt = 1
                .Cells(x, 2).Interior.ColorIndex = 4 + i   '@@@@@@@@@   for checking
                End If
            Next
            If qt = 1 Then
                ListBox2.AddItem (.Cells(x, 2))
                For ii = 1 To 12
                    ListBox2.List(ListBox2.ListCount - 1, ii) = .Cells(x, ii + 2)
                Next
            End If
            qt = 0
        Next
    End If
End With
End Sub

[Kathy93]

YOU ARE AMAZING and A LIFE SAVER! Thank you so much

[mdmackillop]

Happy to help. You can mark this solved using the Thread Tools dropdown