I have a user form with a listbox, textbox, and command button. The intent is for a user to type into the textbox, click the button, and the code would search through the database for the text. database consist of 5 column like below;
1. client
2. province
3.district
4.commune
5. village
im trying to search for the client name example "sorn" or "sorn tye" than it will display all the related information regarding that name in a listbox. for now, the listbox will display all the info in the database not the one that i search in the textbox.
Below is some code I've been trying to change to work. Not sure if this is a step in the right direction.
Code
Private Sub cmdAnalysis_Click()
'dim the variables
Dim FindMe As Range
Dim DataSH As Worksheet
'error handler
On Error GoTo errHandler:
'set object variables
Set DataSH = DataBase
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'//////////////////////////////////////////
'find the value in the column
Columns("D:H").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"AG5:AG6"), CopyToRange:=Range("AI5:AM5"), Unique:=False
Set searchme = Range("AG6")
Set FindMe = Range("D2:H1572").Find(What:=searchme, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
FindMe.Select
'/////////////////////////////////////////
'unprotect all sheets
'Unprotect_All
'filter the data
Columns("D:H").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("AG5:AG6"), CopyToRange:=Range("AI5:AM5"), _
Unique:=False
'add the dynamic data to the listbox
lstAnalysis.RowSource = DataSH.Range("DataList").Address(external:=True)
'protect all sheets
'Protect_All
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets
'Protect_All
'if error occurs then show me exactly where the error occurs
MsgBox "No match found for " & txtSearch.Text
'clear the listbox if no match is found
Me.lstAnalysis.RowSource = ""
Exit Sub
End Sub
Display More