Excel '13 VBA Cascading ComboBox - Trouble get

Question

I finally was able to get combobox2 to load with values that correspond with the selection made in combobox1. The issue is that i can not get only unique values to populate in combobox2. It returns all of the values based on combobox1's selection including the duplicates. I have moved the .clear to various spots in the code but that only changes it from loading multiple duplicate values to showing 1 total value or blank.

Here is the source where I am adapting the code from https://www.youtube.com/watch?v=yMO_wCZgQbc

("DATA") is the worksheet where my data is ("CHART") is the worksheet where my ComboBoxes are cmbRent = ComboBox1 cmbSub = ComboBox2

    Private Sub cmbRent_Change()

MyVal = Me.cmbRent.Value

'loop thru col B
lr = ThisWorkbook.Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row

  'clear cmbSub
    ThisWorkbook.Sheets("CHART").cmbSub.Clear


'loop thru
For x = 2 To lr
    If MyVal = ThisWorkbook.Sheets("DATA").Cells(x, 1) Then
        'add to combobox
   ThisWorkbook.Sheets("CHART").cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(x, 2)
  End If

Next x


      ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1
End Sub

Answer 1

You need to add a check to see if these have already been added to the combobox. I've also used variables for the worksheets for ease of code readability and to make it faster to type.

Dim wsChart As Worksheet
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsChart = ThisWorkbook.Sheets("CHART") 
Set wsData = ThisWOrkbook.Sheets("DATA")

.....(insert rest of code here)

For x = 2 To lr
    If MyVal = wsData.Cells(x, 1) Then
       'add to combobox
        ValueToAdd = wsData.Cells(x,2) 'Get value from worksheet
        If InStr(listOfValues, valueToAdd) = 0 Then
        'Check to see if the value has already been added
        'If not, add to values added and add the item to the combobox.
              listOfValues = listOfValues & ValueToAdd
              wsChart.cmbSub.AddItem valueToAdd
        End If
    End If
Next x