Adding Items into ListBox with combo boxes even combobox is empty

[baha17]

Hi All,
I have a list box in the userform. I would like to add items into ListBox which correspondent with 4 combo box values. If all combo boxes is selected, that would be easy task. However, user might not need to select all combo boxes. If any of the combo boxes empty, my code fails that end up listbox is empty. I could write the code with all of the combinations, but that will be my last choice if there is nothing easier than that. Can I adjust the code such as if the combo box="" then skip then look at the other combo box if that is "" than skip again. If all combos are "" add all the items from the source.
Thank you very much in advance.
Baha

My code is below:

AllStaffLB is listbox
StaffSrchCB is combo box
StaffSrchCB2 is combo box
PosType is combo box
SchTimeCB is combo box

Private Sub StaffSrchCB_Change()
'MATCH(P9,D1:M1,0)
Dim gtype, gtype2 As Variant
Dim gtypeCol, gtypeCol2 As Long
Dim PosType As Variant
Dim cel As Range
Dim LastRow As Long
LastRow = Sheets("Staff").Range("A65536").End(xlUp).Row
gtype = StaffSrchCB.Value
gtype2 = StaffSrchCB2
On Error Resume Next
gtypeCol = Application.WorksheetFunction.Match(gtype, Sheets("Staff").Range("D1:W1"), 0) + 2
gtypeCol2 = Application.WorksheetFunction.Match(gtype2, Sheets("Staff").Range("D1:W1"), 0) + 2
PosType = Application.WorksheetFunction.VLookup(PosCB.Value, Sheets("Staff").Range("D2:F" & LastRow), 6, 0)

With StafRepUF.AllStaffLB
.Clear
For Each cel In Worksheets("Staff").Range("A1:A" & LastRow)
If cel.Offset(0, gtypeCol) <> "" And gtypeCol <> 0 _
And cel.Offset(0, gtypeCol2) <> "" And gtypeCol2 <> 0 _
And cel.Offset(0, 3).Text = SchTimeCB.Text _
And cel.Offset(0, 5).Text = PosType Then
.AddItem cel.Value2
.List(.ListCount - 1, 1) = cel.Offset(0, 7)
.List(.ListCount - 1, 2) = cel.Offset(0, 8)
.List(.ListCount - 1, 3) = cel.Offset(0, 9)
.List(.ListCount - 1, 4) = cel.Offset(0, 10)
.List(.ListCount - 1, 5) = cel.Offset(0, 11)
.List(.ListCount - 1, 6) = cel.Offset(0, 13)
.List(.ListCount - 1, 7) = cel.Offset(0, 15)
.List(.ListCount - 1, 8) = cel.Offset(0, 4)
.List(.ListCount - 1, 9) = cel.Offset(0, 3) & "-" & cel.Offset(0, 5)
End If
Next cel
End With
End Sub


Private Sub UserForm_Initialize()
Dim cel As Range
Dim LastRow As Long
'StafRepUF.AllStaffLB.RowSource = "pits"
LastRow = Sheets("Staff").Range("A65536").End(xlUp).Row
StaffSrchCB.RowSource = "GameType"
StaffSrchCB2.RowSource = "GameType"
PosCB.RowSource = "StaffPos"
SchTimeCB.RowSource = "SchTime"
With StafRepUF.AllStaffLB
.Clear
For Each cel In Worksheets("Staff").Range("A1:A" & LastRow)
.AddItem cel.Value2
.List(.ListCount - 1, 1) = cel.Offset(0, 7)
.List(.ListCount - 1, 2) = cel.Offset(0, 8)
.List(.ListCount - 1, 3) = cel.Offset(0, 9)
.List(.ListCount - 1, 4) = cel.Offset(0, 10)
.List(.ListCount - 1, 5) = cel.Offset(0, 11)
.List(.ListCount - 1, 6) = cel.Offset(0, 13)
.List(.ListCount - 1, 7) = cel.Offset(0, 15)
.List(.ListCount - 1, 8) = cel.Offset(0, 4)
.List(.ListCount - 1, 9) = cel.Offset(0, 3) & "-" & cel.Offset(0, 5)
Next cel
End With

'StafRepUF.AllStaffLB.RowSource = Sheets("Staff").Range("A:C")
End Sub

 

[baha17]

Hi Everybody,

I think I could not write it very clearly. I have a listbox(AllStaffLB) and four comboboxes(StaffSrchCB,StaffSrchCB2,PosCB,SchTimeCB).As you can see in UserForm_Initialize event, listbox will show all the staff details from one worksheet.Now I want to control the listbox(AllStaffLB) list item such as; once any of those 4 combo boxes changes,remove item from the listbox:
StaffSrchCB and StaffSrchCB are the combo boxes for staff skills which supposed to control AllStaffLB.List(from column 2 to 8), listbox column 9 control by SchTimeCB and listbox column 10 is to control by PosCB. I mean I would like to triger combobox change event something like( let's say for SchTimeCB) ; if SchTimeCB.value<> AllStaffLB column9 to remove the item from listbox.I think this should be very easy task but I dont know the correct procedure.
Thank you very much for the help
Baha

 

[baha17]

1