Private Sub CommandButton1_Click()
Dim username As String, id As String, lid As String, mnth As String
Dim ws As Worksheet
Application.ScreenUpdating = False
With Me
username = .TextBox1.Value
id = .TextBox2.Value
lid = .TextBox3.Value
mnth = .ComboBox1.Value
If Not username <> "" Or _
IsError(Application.Match(username, Range("Table_Source[Username]"), 0)) Then
MsgBox "Pls input a valid username.", vbInformation
.TextBox1.SetFocus
GoTo e
End If
If Not id <> "" Or _
IsError(Application.Match(id, Range("Table_Source[ID]"), 0)) Then
MsgBox "Pls input a valid ID.", vbInformation
.TextBox2.SetFocus
GoTo e
End If
If Not lid <> "" Or _
IsError(Application.Match(Val(lid), Range("Table_Source[Login]"), 0)) Then
MsgBox "Pls input a valid LoginID.", vbInformation
.TextBox3.SetFocus
GoTo e
End If
If Not mnth <> "" Or _
IsError(Application.Match(mnth, Range("Table_Source[Month]"), 0)) Then
MsgBox "Pls use dropdown to select a month.", vbInformation
.ComboBox1.SetFocus
GoTo e
End If
End With
'using countif is more easy then match as there is no error trap to be used
With Sheet4
.Visible = xlSheetVisible
.Cells.Clear
End With
With Range("Table_Source")
.AutoFilter 33, username
.AutoFilter 6, id
.AutoFilter 34, lid
.AutoFilter 2, mnth
End With
'Range("Table_Source[#All]").SpecialCells(xlCellTypeVisible).Copy Sheet4.[a1]
Dim crng As Range
With Sheet2.ListObjects("Table_Source")
Set crng = Union(.ListColumns(1).Range, .ListColumns(6).Range, _
.ListColumns(10).Range, .ListColumns(23).Range, _
.ListColumns(25).Range, .ListColumns(26).Range, _
.ListColumns(27).Range, .ListColumns(28).Range, _
.ListColumns(29).Range, .ListColumns(30).Range, _
.ListColumns(33).Range, .ListColumns(34).Range)
End With
crng.Copy Sheet4.[a1]
Range("Table_Source[#All]").AutoFilter
Set crng = Nothing
Unload Me
Sheet4.Activate
If (Application.CountIf(Range("Table3[Username]"), username) + _
Application.CountIf(Range("Table3[ID]"), id) + _
Application.CountIf(Range("Table3[Login]"), lid)) > 0 Then
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Interface" Then ws.Visible = -1
Next
End If
e:
Application.ScreenUpdating = True
End Sub
