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?

 

[My Aswer Is This]

I get stumped on the first line of your question.
You said: "I'm wanting to present the EU with a way to identify who referred a Client to them"

I'm wondering what is "EU" ?

 

[reberryjr]

Sorry. EU = End User

 

[mikerickson]

I'm wondering about "why a combobox"? Its not usually used to display data, its used for the user to select from a list of options.

You also don't mention where you what this combo box? On a user form, on a worksheet? Or how you want this box to "track back" multiple referrals.

Edit: I should also mention that your style of ID number may prove problematic as Excel might confuse it with a cell address.

 

[reberryjr]

Well, the thought of a combobox is to display the options to the EU, as I don't expect them to remember what every Client's ID is. The other option that I can think of is to tie the Name and Nickname fields to the ID, so that when they select a value from the RefCat combobox, the RefName and RefNickname populate with the associated values. I'm not sure what the best approach is, and I'm not sure how to code for either.

 

[mikerickson]

This assumes that you have your data as shown in the OP, on Sheet1, with the header "Client ID" in cell A1 and "Client Name" and "Client Nickname" in columns B and C. If the sheet name is different, adjust the function DataRange. If the columns are different, adjust the .Cells chosen in the Initalize event.

It also assumes that you are entering data into a user form that has (among other controls) two comboboxes, cbxRefCat and cbxRefId and two textbooks tbxRefName and tbxRefNickname. (Adjust the Intialize event if the referral cateogories are other than "client", "friend", "self" or "other")

When the user form is loaded, cbxRefID will be filled with the (sorted) list of client IDs from the data range. When one of those is selected by the user, the textbooks tbxRefName and tbxRefNickname will be filled with the data for that selected client.

cbxRefID, tbxRefName and tbxRefNickname will not be visible until "Client" is selected from the cbxRefCat combobox.

Private Sub cbxRefCat_Change()
    If LCase(cbxRefCat.Text) = "client" Then
        cbxRefID.Visible = True
    Else
        cbxRefID.ListIndex = -1
        cbxRefID.Visible = False
    End If
    Me.tbxRefName.Visible = cbxRefID.Visible
    tbxRefNickname.Visible = cbxRefID.Visible
End Sub

Private Sub cbxRefID_Change()
    Dim refName As String, refNickname As String
    With cbxRefID
        If .ListIndex <> -1 Then
            refName = .List(.ListIndex, 1)
            refNickname = .List(.ListIndex, 2)
        End If
    End With
    Me.tbxRefName = refName
    Me.tbxRefNickname = refNickname
End Sub

Private Sub UserForm_Initialize()
    Dim myData As Range, oneID As String
    Dim i As Long, j As Long
    Set myData = DataRange.Resize(, 3)
    Me.cbxRefID.ColumnCount = 4: Rem design time
    Me.cbxRefID.ColumnWidths = "50;;;0": Rem design time
    
    Rem fill Referer Category combobox
    Me.cbxRefCat.List = Array("Client", "Friend", "self", "other")
    
    Rem fill Referer ID combobox in alphabetical order
    For i = 1 To myData.Rows.Count
        oneID = myData.Cells(i, 1).Value
        With cbxRefID
            For j = 0 To .ListCount - 1
                If LCase(oneID) < LCase(.List(j, 0)) Then
                    Exit For
                End If
            Next j
            .AddItem oneID, j
            .List(j, 1) = myData.Cells(i, 2).Value
            .List(j, 2) = myData.Cells(i, 3).Value
            .List(j, 3) = myData.Cells(i, 1).Address(, , , True)
        End With
    Next i
    With Me
        .cbxRefID.Visible = False
        .tbxRefName.Visible = False
        .tbxRefNickname.Visible = False
    End With
    
End Sub

Function DataRange() As Range
    With Sheet1.Range("A:A")
        Set DataRange = Range(.Cells(2, 1), Cells(.Rows.Count, 1).End(xlUp))
    End With
End Function

 

[reberryjr]

@mikerickson Thank you for the info. I think where this presents a challenge for me is, that I want the Referral Nickname combobox to be available all the time. That way, if the referring party isn't a Client, the EU (End User) is able to document the referring party's name. I'm kind of thinking something along the lines of a Scripting Dictionary that auto-populates the Referral ID (which is in a combobox, tied to a named range); when a referral name is selected. So, if the referring party's nickname is "Tommy", the EU would select Tommy from the Referral Nickname combobox, and then Tommy's ID of "TJ1" would pop up.

Here's how the data is set up on the main worksheet (some columns are hidden). Client ID and Nickname are named ranges.

Client ID	First	Last	Suffix	Name	Nickname	Referral Category	Referred By ID	Referred By Name
TJ1	Tom	Jones	Sr.	Tom Jones Sr.	Tommy
ND2	Nancy	Drew		Nancy Drew	Neener	Client	TJ1	Tommy
CH3	Charles	Harper		Charles Harper	Chuck	Client	TJ1	Tommy

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

 

[mikerickson]

Do you want the combo box to be for RefererID or RefererName?

 

[reberryjr]

Because I think that the EU is more likely to remember nicknames than ids, probably the nickname. I've only used scripting dictionary once, and that was from code that some sent me, so I'm not entirely sure what it does, or how it works. Maybe that's not the best route to go. If you would, suggest a path, and I'll research it and see if I can get it to work. If I can't, I'll circle back.

 

[mikerickson]

In the OP, it was anticipated that the RefererID would be entered and you wanted the Name and Nickname returned.
In post 7, you mention the Name as being what the user would enter.
In your last post, you talk about the user entering the Nickname of the referrer.

I think that if you are clear in your instructions, the user will be able to enter whichever data specified (ID, Name or Nickname). But, you need to decide which of those data are what you want them to enter. ("Clear in your instructions" includes the possibility of having the two dependent fields displayed with a Label in the userform, making it impossible for the user to enter them.)