frmCustomerSearch.bas

Option Explicit

Dim wsReferences As Worksheet, wsFilter As Worksheet

Private Sub cmdClear_Click()
    Me.lstResults.Clear
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub cmdSearch_Click()
    
    Dim LastRow As Long, RowNumber As Long, RowTracker As Long
    Dim searchValue As String
    Dim currentSelection As Range
    
    On Error GoTo errHandle
    Set currentSelection = Selection
    
    searchValue = Me.txtSearchValue.Value
    
    If Len(Trim(searchValue)) = 0 Then
        Me.lblStatus.Caption = "Search field is empty"
        GoTo CleanUp
    Else
        Me.lblStatus.Caption = ""
        Me.lstResults.Clear
    End If
    
    With wsReferences
        
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        
        RowTracker = 0
        
        For RowNumber = 4 To LastRow
            If .Cells(RowNumber, "C").Value Like "*" & searchValue & "*" Then
                
                Me.lstResults.AddItem
                Me.lstResults.List(RowTracker, 0) = .Cells(RowNumber, "C").Value '// Entering customer name
                Me.lstResults.List(RowTracker, 1) = .Cells(RowNumber, "B").Value '// Entering cusomer ID
                
                RowTracker = RowTracker + 1
            End If
        
        Next RowNumber
        
    End With
    
CleanUp:
    Set currentSelection = Nothing
Exit Sub

errHandle:
    MsgBox "Error: " & Err.Description, vbExclamation
End Sub

Private Sub cmdSelect_Click()
    
    Dim SelectionRow As Long, RowIndex As Long
    
    SelectionRow = Selection.Row
    
    If Selection.Worksheet.Name <> "Filter" Then
        Me.lblStatus.Caption = "Incorrect worksheet"
        Exit Sub
    ElseIf Selection.Column <> 2 And Selection.Column <> 3 Then
        Me.lblStatus.Caption = "Invalid column"
        Exit Sub
    ElseIf 5 > SelectionRow Then
        Me.lblStatus.Caption = "Invalid row"
        Exit Sub
    Else
        Me.lblStatus.Caption = ""
    End If
    
    If Me.lstResults Is Nothing Or IsNull(Me.lstResults) Then
        Me.lblStatus.Caption = "No customer is selected"
        Exit Sub
    Else
        Me.lblStatus.Caption = ""
        RowIndex = Me.lstResults.ListIndex
        
        wsFilter.Cells(SelectionRow, "B") = Me.lstResults.List(RowIndex, 0)
        wsFilter.Cells(SelectionRow, "C") = Me.lstResults.List(RowIndex, 1)
    End If
    
End Sub

Private Sub UserForm_Initialize()
    
    Set wsReferences = ThisWorkbook.Worksheets("References")
    Set wsFilter = ThisWorkbook.Worksheets("Filter")

End Sub

Private Sub UserForm_Terminate()
    
    Set wsReferences = Nothing
    Set wsFilter = Nothing
    
End Sub