Removing Duplicates from ComboBox and Populating one combobox based on another

[kbmtech]

Hello everyone,

First let me say that your team of specialists has help my job a lot easier, and I have learned quite a lot from your advise, for which I am very thankful. Now on to the task at hand, I am trying to "remodel" our purchasing agents database, and I need help with combo box functionality. Basically, I am populating my First Name and Last Name combo boxes based on ranges in my spreadsheet. However, First or Last Names may duplicate. I would like my combo box to use only unique instances for the first name, then based on the first name selection, populate the last name combo box. Then based on the combination of First and last name, provide the remaining information in the available text boxes. I have created custom search forms before but typically only one combo box, and because the amount of purchasing agents is over 200, a single combo box with first and last name is not very practical. Any help you can lend me is very welcomed. I have attached a sample file with bogus data for your convenience.

Below is the code I am using to initialize my form

Private Sub UserForm_Initialize()
Dim lFirst As Range
Dim lLast As Range
Dim ws As Worksheet
Set ws = Worksheets("Accounts")


For Each lFirst In ws.Range("FirstList")
  With Me.cboFirst
    .AddItem lFirst.Value
    .List(.ListCount - 1, 1) = lFirst.Offset(0, 1).Value
  End With
Next lFirst

For Each lLast In ws.Range("LastList")
  With Me.cboLast
    .AddItem lLast.Value
  End With
Next lLast

Me.cboFirst.SetFocus
End Sub