Maybe something like this that you want ?
Private Sub SEARCH_Click()
Sheets("Tracker").Range("A:D").AutoFilter
ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:=DEPT.Value
If OptionButton1.Value = True Then
Set c = Range("C:C").Find("In")
If Not c Is Nothing Then
ActiveSheet.Range("A:D").AutoFilter Field:=3, Criteria1:="In"
Else
MsgBox "There is no IN status"
End If
End If
If OptionButton2.Value = True Then
Set c = Range("C:C").Find("Out")
If Not c Is Nothing Then
ActiveSheet.Range("A:D").AutoFilter Field:=3, Criteria1:="Out"
Else
MsgBox "There is no OUT status"
End If
End If
Call PopLB
End Sub
Private Sub CLEAR_Click()
Sheets("Tracker").Range("A:D").AutoFilter
Call PopLB
End Sub
Private Sub UserForm_Initialize()
Sheets("Tracker").Range("A:D").AutoFilter
Call PopLB
End Sub
Sub PopLB()
Set mysheet = ThisWorkbook.Sheets("Tracker")
'lastrow = mysheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = mysheet.Range(("a2"), mysheet.Range("a" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
With lstTools
.CLEAR
.ColumnCount = 4
.ColumnWidths = "30,50,40,40"
End With
For Each cell In Rng
'For x = 2 To lastrow
With lstTools
.AddItem cell
.List(.ListCount - 1, 1) = cell.Offset(0, 1)
.List(.ListCount - 1, 2) = cell.Offset(0, 2)
.List(.ListCount - 1, 3) = cell.Offset(0, 3)
'.List(.ListCount - 1, 0) = mysheet.Cells(x, 1)
'.List(.ListCount - 1, 1) = mysheet.Cells(x, 2)
'.List(.ListCount - 1, 2) = mysheet.Cells(x, 3)
'.List(.ListCount - 1, 3) = mysheet.Cells(x, 4)
End With
Next
End Sub
2020-06-01_16-17-24.gif
You need to name your "search" button to SEARCH,
name the text box for Department as DEPT
And also need to add one button with name CLEAR.
Bookmarks