Jober
07-07-2023, 10:30 AM
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
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