Selecting item in multi-column listbox

B

BarefootPaul

Board Regular
Joined
Jul 21, 2011
Messages
54
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.

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).

Code: 
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
Here it is, in case anyone's curious:

Code: 
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
 
Upvote 0
BarefootPaul said:
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.
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Filter a listbox containing 12 columns using a single searchbox
Replies
8
Views
774
Anthony47
A
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
592
pure vito
P
A
  • Solved
no experience for populate data in listbox based on selected item from another listbox
2
Replies
10
Views
450
Amer Omar
A
A
  • Solved
Listbox data copy to worksheet base on selected columns.
Replies
3
Views
814
DanteAmor
DanteAmor
R
  • Question Question
Delete multi select rows from listbox
Replies
0
Views
629
Rfriend
R

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top