VBA User Form - Multiple Criteria Search

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
185
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0

Forum statistics

Threads
1,216,297
Messages
6,129,955
Members
449,545
Latest member
SURY

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
Back
Top