Multi List ComboBox That Maps Back in Individual Parts

[reberryjr]

I'm wanting to present the EU with a way to identify who referred a Client to them. If another Client did, then I want to track how many referrals that Client has sent the EU's way. Client's have the following identifiers: Name, Nickname and Client ID. All 3 are already named ranges and of the 3, Client ID is the only unique identifier. The issue at hand is, I don't expect the EU to have to remember every Client's ID. Instead, I was thinking maybe present them with a 3 column combo box.

When the EU selects the value they want, I would want the 3 parts of the combo box to map back to a worksheet, in their separate parts.

I did some googling, but couldn't find an example that walked through all of these parts.

Here's how this part of the worksheet is structured. *I did remove some unimpacted columns.

[TABLE="class: grid, width: 750"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Client ID[/TD]
[TD]Name[/TD]
[TD]Nickname[/TD]
[TD]Referral Category[/TD]
[TD]Referred By ID[/TD]
[TD]Referred By Name[/TD]
[/TR]
[TR]
[TD]TJ1[/TD]
[TD]Tom Jones Sr.[/TD]
[TD]Tommy[/TD]
[TD]Friend[/TD]
[TD][/TD]
[TD]Moonshine[/TD]
[/TR]
[TR]
[TD]ND2[/TD]
[TD]Nancy Drew[/TD]
[TD]Neener[/TD]
[TD]Client[/TD]
[TD]TJ1[/TD]
[TD]Tommy[/TD]
[/TR]
</tbody>[/TABLE]

An example of what I'm looking for is, when Client "ND2" was added. I would have wanted a combobox to present
TJ1 Tom Jones Sr. Tommy

Is there a better route to accomplish my goal? If so, please advise. If not, can someone offer some guidance on how to solve this?

 

[reberryjr]

Sorry about that. Between the OP and Post 7, I made some structural changes, hence the change. Here are my thoughts...If the referring party is a Client, then Client is selected from cobo_RefCat. The cobo_RefNickname box should already have an inventory of nicknames that have been assigned to Clients. If they are a Client, I would want the cobo_RefID box to auto-populate the correct Client ID that is assigned to the referring Client.

Referred by Tom Jones, a Client
cobo_RefCat = Client, as selected by the EU
cobo_RefNickname = Tommy, as selected by the EU (this is already populated on initialization of the form)
cobo_RefID = TJ1 (this is one part of my struggle...tying the RefID to the RefNickname)

Referred by Smitty, not a Client
cobo_RefCat <> Client, as selected by the EU
cobo_RefNickname = Smitty, as entered by the EU (this isn't sourced from anywhere, as I'm only housing Client information at this point, so it would have to be typed in)
cobo_RefID = "" (this wouldn't be required, since there is no assigned ID)

Hopefully that makes more sense.

 

[reberryjr]

@mikerickson I believe that I've figured it out. This code seems to be working.

Private Sub cobo_RefNickname_Change()
Dim ws3 As Worksheet
Dim FindRow As Range
Dim RefNick As Long
Set ws3 = ThisWorkbook.Sheets("Bios")
With ws3
'Finds the row where the value of the cobo_RefNickame exists, and populates the RefID combobox with the value of the Client ID, from the same row.
Set FindRow = Range("J:J").Find(What:=Me.cobo_RefNickname, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow Is Nothing Then
    RefNick = FindRow.Row
End If
End With
Me.cobo_RefID = ws3.Range("E" & RefNick).Value
End Sub