VBA User Form - Multiple Criteria Search

[Denny57]

I am working on the creation of a membership database with a VBA User Form through which I am looking to add, amend and search for records that are either not unique or which might be found by searching either by a single or multiple elements.
I have been able to create a search function based on just a single unique record option but I would like to allow a search similar the "Criteria" options available though the in-built "Form" command available in Excel (MS 365 version). Unfortunately, the database requires more columns than the Excel "Form" command permits.

I am hoping that VBA allows for the inclusion of a command button (such as "Criteria") that when selected would allow the user to input one or more search values and then have both "Find Next" & "Find Previous" buttons which would return the corresponding records that meet the matching criteria.

Any help would be most welcome..

 

[Sourdough123]

this my code can you help to extend it to two criteria please....i want it to filter only "InStock" items in Column 18

Can you help Me
Please
BB

Sub SearchData()

Application.ScreenUpdating = False

Dim shDatabase As Worksheet ' Database sheet

Dim shSearchData As Worksheet 'SearchData sheet

Dim iColumn As Integer 'To hold the selected column number in Database sheet

Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database sheet

Dim iSearchRow As Long 'To hold the last non-blank row number available in SearachData sheet

Dim sColumn As String 'To store the column selection

Dim sValue As String 'To hold the search text value

Set shDatabase = ThisWorkbook.Sheets("Database")

Set shSearchData = ThisWorkbook.Sheets("SearchData")

iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row

sColumn = frmForm.cmbSearchColumn.Value

sValue = frmForm.txtSearch.Value



iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A4:X4"), 0)

'Remove filter from Database worksheet

If shDatabase.FilterMode = True Then

shDatabase.AutoFilterMode = False

End If

'Apply filter on Database worksheet

If frmForm.cmbSearchColumn.Value = "Supplier Num" Then

shDatabase.Range("A4:AY" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue

Else

shDatabase.Range("A4:AY" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"

End If

If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("A:AY")) >= 2 Then

'Code to remove the previous data from SearchData worksheet

shSearchData.Cells.Clear



shDatabase.AutoFilter.Range.Copy shSearchData.Range("A4")

Application.CutCopyMode = False

iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row

frmForm.lstDatabase.ColumnCount = 50

' frmForm.lstDatabase.ColumnWidths = "0,0,60,60,60,60,45,110,70,70,60,60,60,60,60,60,60,60,60,70,0,0,60,60,60"

If iSearchRow > 1 Then

frmForm.lstDatabase.RowSource = "SearchData!A5:AY" & iSearchRow

MsgBox "Records found."

End If

Else

MsgBox "No record found."

End If

shDatabase.AutoFilterMode = False

Application.ScreenUpdating = True

End Sub