Filtering listbox with combobox text

[baha17]

HI Everybody,

I have a data sheet which has the skill codes of our staff in the casino. These are gaming skills such as; BJ,MD,NBC and so on
I have a comboboxes which filters the data in the listbox list which works under "listbox.change" event. Whenever I change the combobox text to let say "BJ" (black jack; I know eveybody likes this game) all the staff who does not have "BJ" skill will be removed from the list.It works partially fine upto now. Just recently I start adding N infront to identify the newly coded staff. So if the staff is just recently trained BJ it will appear NBJ. But now, when I change the combo text to "BJ", staff who has "NBJ" are also removed from list box. I have around 20 game skills and I do not want to write all possible scenarios as select case, I am hoping some of you excel masters can give me a hand over here. Belowis my code in combo change event, thank you for the help
Baha

Private Sub StaffSrchCB_Change()
    Dim N&
    Dim vListData
    Dim gtype As Variant
    Dim gtypeCol As Long
    Dim cel As Range
    Dim LastRow As Long
    gtype = StaffSrchCB.Value
    On Error Resume Next
    gtypeCol = Application.WorksheetFunction.Match(gtype, Sheets("Staff").Range("A1:W1"), 0) - 1
    
   'Exit Sub
   
    LastRow = Sheets("Staff").Range("A65536").End(xlUp).Row
    
    If StaffSrchCB.Text = "All" Or StaffSrchCB.Text = "" Then
    StaffSrchCB2 = ""
    SchTimeCB = ""
    PosCB = ""
    StaffSrchCB = ""
    AllStaffLB.Clear
    vListData = Worksheets("Staff").Range("A2:U" & LastRow)
    StafRepUF.AllStaffLB.List = vListData
    Else
    
    With AllStaffLB
    Select Case Len(StaffSrchCB.Text)
    Case Is = 3
    For N = (.ListCount - 1) To 0 Step -1
        If Left(UCase(.List(N, gtypeCol)), 3) <> UCase(StaffSrchCB.Text) Then .RemoveItem (N)
    Next
    Case Else
    For N = (.ListCount - 1) To 0 Step -1
        If Left(UCase(.List(N, gtypeCol)), 2) <> UCase(StaffSrchCB.Text) Then .RemoveItem (N)
    Next
    End Select
    End With
    
    End If
End Sub

 

[baha17]

Hi,
I figured out by my self.Sorry for disturbance....

Private Sub StaffSrchCB_Change()
Dim N&
Dim vListData
Dim gtype As Variant
Dim gtypeCol As Long
Dim cel As Range
Dim LastRow As Long
gtype = StaffSrchCB.Value
On Error Resume Next
gtypeCol = Application.WorksheetFunction.Match(gtype, Sheets("Staff").Range("A1:W1"), 0) - 1

'Exit Sub

LastRow = Sheets("Staff").Range("A65536").End(xlUp).Row

If StaffSrchCB.Text = "All" Or StaffSrchCB.Text = "" Then
StaffSrchCB2 = ""
SchTimeCB = ""
PosCB = ""
StaffSrchCB = ""
AllStaffLB.Clear
vListData = Worksheets("Staff").Range("A2:U" & LastRow)
StafRepUF.AllStaffLB.List = vListData
Else

With AllStaffLB
Select Case Len(StaffSrchCB.Text)
Case Is = 3
For N = (.ListCount - 1) To 0 Step -1
Select Case Left(UCase(.List(N, gtypeCol)), 1)
Case Is = "N"
If Mid(UCase(.List(N, gtypeCol)), 2, 3) <> UCase(StaffSrchCB.Text) Then .RemoveItem (N)
Case Else
If Left(UCase(.List(N, gtypeCol)), 3) <> UCase(StaffSrchCB.Text) Then .RemoveItem (N)
End Select
Next
Case Else
For N = (.ListCount - 1) To 0 Step -1
Select Case Left(UCase(.List(N, gtypeCol)), 1)
Case Is = "N"
If Mid(UCase(.List(N, gtypeCol)), 2, 2) <> UCase(StaffSrchCB.Text) Then .RemoveItem (N)
Case Else
If Left(UCase(.List(N, gtypeCol)), 2) <> UCase(StaffSrchCB.Text) Then .RemoveItem (N)
End Select
Next
End Select
End With

End If
End Sub