VBA User Form - Multiple Criteria Search

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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..
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Sourdough123

New Member
Joined
Oct 25, 2021
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,514
Messages
5,854,173
Members
431,623
Latest member
ncorkren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top