Selecting item in multi-column listbox

[BarefootPaul]

I have a listbox that pulls in some records based on a last name selected in a combobox. It works like a charm and here is the code.

With wsClients
    Set rSource = .Range("D5", Range("D" & lLastRow).End(xlDown))
    lstClients.Clear
    sLName = cboLName.Value
    For Each rCell In rSource
        If rCell.Value = sLName Then
            sFound = rCell.Address
            Set rFound = Range(sFound)
            Set g_rFound = rFound 'global variable used elsewhere in the userform code
            sBillingID = rFound.Offset(0, -3).Value
            sMaineCare = rFound.Offset(0, -2).Value
            sFName = rFound.Offset(0, -1).Value
            dDOB = rFound.Offset(0, 1).Value
 
            With lstClients
                .AddItem
                .Column(0, lstClients.ListCount - 1) = rFound
                .Column(1, lstClients.ListCount - 1) = sBillingID
                .Column(2, lstClients.ListCount - 1) = sMaineCare
                .Column(3, lstClients.ListCount - 1) = sFName
                .Column(4, lstClients.ListCount - 1) = sLName
                .Column(5, lstClients.ListCount - 1) = dDOB
            End With
 
        End If
    Next rCell
    If lstClients.ListCount > 0 Then
        lstClients.Height = lstClients.ListCount * 18
        lstClients.Visible = True
    Else
        lblNoMatches.Visible = True
    End If
End With

The problem is selecting the record and then populating the corresponding data into textboxes on the userform. I would like to set the g_rFound variable when I select the record (rather than doing it when populating the listbox, as I am currently). I haven't figured out how to do this from the reading on the forum and some other resources I have.

I am populating the textboxes like this (below), currently, and it works if there is only 1 matching last name in the dataset. Otherwise it always pulls the last record (since that is when I set the g_rFound variable).

txtFName.Value = g_rFound.Offset(0, -1).Value
txtLName.Value = g_rFound.Value
txtBillingID.Value = g_rFound.Offset(0, -3).Value
txtMaineCare.Value = g_rFound.Offset(0, -2).Value
txtDOB.Value = g_rFound.Offset(0, 1).Value

Any help/direction is highly appreciated. I'm using Excel 2010 on Windows 7.

 

[Jerry Sullivan]

Is there a reason that you aren't using a unique value (perhaps sBillingID) as the key to finding the correct row to use when populating the TextBoxes?

 

[BarefootPaul]

Actually I am, sort of. I have it set up so the end user can search by BillingID, MaineCare#, or last name. The first two are unique, but are not easily remembered. I ended up figuring out a way to do it, but don't have it right in front of me (its on my work computer). I'll try to post it later today.

 

[BarefootPaul]

Here it is, in case anyone's curious:

Dim wsClients As Worksheet
Dim rSource As Range
Dim lLastRow As Long
Dim r As Integer
Dim sFName As String
Dim sLName As String
Dim sBillingID As String
Dim sMaineCare As String
Dim dDOB As Date
 
Set wsClients = ActiveWorkbook.Sheets("Clients")
lLastRow = wsClients.UsedRange.Rows.Count
Set rSource = wsClients.Range("A5", Range("A" & lLastRow).End(xlDown))
 
For r = 0 To lstClients.ListCount - 1
    If lstClients.Selected(r) Then
        g_rFound = lstClients.List(r, 0)
        sBillingID = lstClients.List(r, 1)
        sMaineCare = lstClients.List(r, 2)
        sFName = lstClients.List(r, 3)
        sLName = lstClients.List(r, 4)
        dDOB = lstClients.List(r, 5)
    End If
Next r
 
With rSource
    Set g_rFound = .Find(What:=sBillingID, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
End With
 
txtFName.Value = sFName
txtLName.Value = sLName
txtBillingID.Value = sBillingID
txtMaineCare.Value = sMaineCare
txtDOB.Value = dDOB

 

[Jerry Sullivan]

Actually I am, sort of. I have it set up so the end user can search by BillingID, MaineCare#, or last name. The first two are unique, but are not easily remembered.

That helps to clarify why you want to allow the option to search by Last Name instead of the BillingID, MaineCare#.

What are you wanting the code to do when there are more than one matching Last Names?

Your code is trying to populate the TextBoxes with data associated with a single record. If you are going to allow a search by Last Name, it would appear you'll need some intermediate step that shows the user a list of matches and has them make a selection.

 

[BarefootPaul]

Yes, that is the first bit of code that I included in my original post on this thread. It creates a multi-column listbox (lstClients) and adds the records to it, sets the the size of the listbox based on the number of records and then makes it visible for the end user then to choose from.

Paul