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.

Client ID	Name	Nickname	Referral Category	Referred By ID	Referred By Name
TJ1	Tom Jones Sr.	Tommy	Friend		Moonshine
ND2	Nancy Drew	Neener	Client	TJ1	Tommy

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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