Return userform values based on 2 search criteria

cking1333 

Here is an example of how you can change your code to include both the customer name and the serial number as search criteria. The VBA code is not tested, please save your file beforehand.

Dim ws As Worksheet
Dim rng As Range
Dim f As Range
Dim found As Boolean
Dim clientName As String
Dim serialNumber As String
Dim answer As Integer

With SearchClient
    clientName = .Value  ' Assuming SearchClient is the textbox for client name
    serialNumber = SearchSerialNumber.Value  ' Assuming SearchSerialNumber is the textbox for serial number
    
    Set ws = Sheets("CLIENT PROFILES")
    Set rng = ws.Range("A4:A1503")  ' Assuming client names are in Column A
    
    found = False
    
    For Each f In rng
        If LCase(f.Value) = LCase(clientName) And LCase(ws.Cells(f.Row, "B").Value) = LCase(serialNumber) Then ' Assuming serial numbers are in Column B
            ' Populate the form with the found client's information
            ClientNameModifyProfile.Caption = ws.Range("C" & f.Row).Value
            BadgeModifyProfile.Caption = ws.Range("E" & f.Row).Value
            ActiveOfficerModifyProfile.Caption = ws.Range("F" & f.Row).Value
            ActiveClientGroup.Value = ws.Range("O" & f.Row).Value
            NotesClientProfile.Value = ws.Range("J" & f.Row).Value
            HomePositionClientProfile.Value = ws.Range("G" & f.Row).Value
            HomeUnitClientProfile.Value = ws.Range("H" & f.Row).Value
            HomeLocationClientProfile.Value = ws.Range("I" & f.Row).Value
            TempPositionType.Caption = ws.Range("K" & f.Row).Value
            TempPosition.Caption = ws.Range("L" & f.Row).Value
            TempUnit.Caption = ws.Range("M" & f.Row).Value
            TempLocation.Caption = ws.Range("N" & f.Row).Value
            found = True
            Exit For
        End If
    Next f
    
    If Not found Then
        MsgBox "No Client Profile exists for this individual with the specified serial number."
        Exit Sub
    End If
End With

Important Considerations:

• Control Names: Make sure the control names in your user form (SearchClient, SearchSerialNumber, etc.) match the names used in the code.
• Data Columns: Ensure that the data columns (e.g., where the client name and serial number are stored) in the worksheet match those referenced in the code.
• Case Sensitivity: The LCase function is used to make the search case-insensitive. If case sensitivity is required, remove the LCase function.

This code should allow you to search based on both client name and serial number, ensuring that the correct equipment for the client is displayed. The code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.