Command Button

[Jober]

Have a database that user types in info and listbox shows data from database (search function). My problem is I can't seem to turn of the filter function on the database columns. I tried multiple things but nothing.
I do have a reset button that I can use but can't seem to find the correct code to write. Reason i need to have this clear is user may need to search and search again, but of course with filter set on column may remove data in another column.

I want to use the reset button remove filters. Below is the code for the search function.


Private Sub cmd1_Click()
If Me.TextBox1.Value = "" Then
MsgBox "Please enter search value.", vbOKOnly + vbInformation, "Search"
Exit Sub
End If
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim sht As Worksheet
Set sh = ThisWorkbook.Sheets("database")
Set sht = ThisWorkbook.Sheets("searchdata")
Dim iColumn As Integer
Dim ish As Long
Dim isht As Long
ish = ThisWorkbook.Sheets("database").Range("A" & Application.Rows.Count).End(xlUp).Row
If Me.ComboBox1.Value = Empty Then
MsgBox "Please select search criteria."
Exit Sub
End If
iColumn = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("A1:O1"), 0)
If sh.FilterMode = True Then
sh.AutoFilterMode = False
End If
If Me.ComboBox1.Value = "ACTUAL_SIZE" Then
sh.Range("A2:O" & ish).AutoFilter Field:=iColumn, Criteria1:=Me.TextBox1.Value
Else
sh.Range("A2:O" & ish).AutoFilter Field:=iColumn, Criteria1:="*" & Me.TextBox1.Value & "*"
End If
sht.Cells.Clear
sh.AutoFilter.Range.Copy sht.Range("A1")
Application.CutCopyMode = False
isht = sht.Range("A" & Application.Rows.Count).End(xlUp).Row
Me.ListBox1.ColumnCount = 15
Me.ListBox1.ColumnWidths = "65,65,65,65,65,65,65,65,65,65,65,65,65,65,65"
If isht > 1 Then
Me.ListBox1.RowSource = "searchdata!A2:P" & isht
MsgBox "Records found"
Else
MsgBox "No records found"
End If
sh.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

[June7]

FYI, should post code between CODE tags to retain indentation and readability.

Try using macro recorder to get basic code then adapt.

[Aussiebear]

I want to use the reset button remove filters. Below is the code for the search function.

If you want to go down the line of a reset button then you could add the following code to a button I guess



Public Sub ClearFilter()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub


However as you already have some code, you might simply add one line.


Private Sub cmd1_Click()
If Me.TextBox1.Value = "" Then
MsgBox "Please enter search value.", vbOKOnly + vbInformation, "Search"
Exit Sub
End If
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim sht As Worksheet
Set sh = ThisWorkbook.Sheets("database")
Set sht = ThisWorkbook.Sheets("searchdata")
Dim iColumn As Integer
Dim ish As Long
Dim isht As Long
ish = ThisWorkbook.Sheets("database").Range("A" & Application.Rows.Count).End(xlUp).Row
If Me.ComboBox1.Value = Empty Then
MsgBox "Please select search criteria."
Exit Sub
End If
iColumn = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("A1:O1"), 0)
If sh.FilterMode = True Then
sh.AutoFilterMode = False
End If
If Me.ComboBox1.Value = "ACTUAL_SIZE" Then
sh.Range("A2:O" & ish).AutoFilter Field:=iColumn, Criteria1:=Me.TextBox1.Value
Else
sh.Range("A2:O" & ish).AutoFilter Field:=iColumn, Criteria1:="*" & Me.TextBox1.Value & "*"
End If
sht.Cells.Clear
sh.AutoFilter.Range.Copy sht.Range("A1")
Application.CutCopyMode = False
isht = sht.Range("A" & Application.Rows.Count).End(xlUp).Row
Me.ListBox1.ColumnCount = 15
Me.ListBox1.ColumnWidths = "65,65,65,65,65,65,65,65,65,65,65,65,65,65,65"
If isht > 1 Then
Me.ListBox1.RowSource = "searchdata!A2:P" & isht
MsgBox "Records found"
Else
MsgBox "No records found"
End If
sh.AutoFilterMode = False
Sh.ShowAllData
Application.ScreenUpdating = True
End Sub

[Aflatoon]

Your code already clears the filters with its last line, unless either of the two Exit Sub lines are executed.