PDA

View Full Version : [SOLVED] Excel Userform Directory - search button using checkboxes



Kathy93
10-03-2017, 01:39 PM
Hi :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 :think: 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 :friends: :hi:

mdmackillop
10-03-2017, 04:13 PM
Rename CommandButton6 to CommandButton1

Kathy93
10-04-2017, 09:58 AM
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
10-04-2017, 10:30 AM
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
10-04-2017, 10:38 AM
YOU ARE AMAZING and A LIFE SAVER! Thank you so much :)

mdmackillop
10-04-2017, 10:39 AM
Happy to help. You can mark this solved using the Thread Tools dropdown