Forum Discussion

cking1333's avatar
cking1333
Copper Contributor
Aug 08, 2024

Return userform values based on 2 search criteria

Hi all,

 

I am using the code below through a userform that will populate labels, textboxes, etc with client information based on the client name in column A (i.e. client location, badge #, active status, etc.).  Each client has different types of equipment (i.e. batons, handcuffs, etc) and each piece of equipment has a unique serial number for individual clients however there may be a risk that there could be duplicate serial numbers across all clients. 

 

My question is this: Is there a way to add additional criteria to the code below to narrow down search results within the spreadsheet to include client name and serial number? This would ensure that users are able to display the proper equipment for the client. 

 

Thanks in advance!

 

 

  Dim f As Range
  Dim ws As Worksheet
  Dim rng As Range
  Dim answer As Integer
  
  With SearchClient
    Set f = Sheets("DTT").Range("D4:D1503").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
        ClientNameModifyProfile.Caption = Sheets("CLIENT PROFILES").Range("C" & f.Row).Value
        BadgeModifyProfile.Caption = Sheets("CLIENT PROFILES").Range("E" & f.Row).Value
        ActiveOfficerModifyProfile.Caption = Sheets("CLIENT PROFILES").Range("F" & f.Row).Value
        ActiveClientGroup.Value = Sheets("CLIENT PROFILES").Range("O" & f.Row).Value
        NotesClientProfile.Value = Sheets("CLIENT PROFILES").Range("J" & f.Row).Value
        HomePositionClientProfile.Value = Sheets("CLIENT PROFILES").Range("G" & f.Row).Value
        HomeUnitClientProfile.Value = Sheets("CLIENT PROFILES").Range("H" & f.Row).Value
        HomeLocationClientProfile.Value = Sheets("CLIENT PROFILES").Range("I" & f.Row).Value
        TempPositionType.Caption = Sheets("CLIENT PROFILES").Range("K" & f.Row).Value
        TempPosition.Caption = Sheets("CLIENT PROFILES").Range("L" & f.Row).Value
        TempUnit.Caption = Sheets("CLIENT PROFILES").Range("M" & f.Row).Value
        TempLocation.Caption = Sheets("CLIENT PROFILES").Range("N" & f.Row).Value
    Else
      MsgBox "No Client Profile exists for this individual."
      Exit Sub
    End If
  End With

 

 

 

 

Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 08, 2024

    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.