VBA 2 comboboxes with NOT-unique values

[MarioExcelNoob]

hello all

I've been searching for this silly issue for 2 hours online now, on all kind of forums, but without success....
So i'm making this post, hoping someone can help me with my issue.

It shouldn't be very hard, but for some reason, i keep missing something.

Basicly i have a userform with 2 comboboxes. cmbPostalcode and cmbCity.
Now, in Belgium we can have multiple cities with the same postalcode (mainly if smaller villages around bigger cities).
The goal is simple enough: select a postal code, fill the second combobox with only the cities corresponding to that postal code (could be 1 city, could be 5...)

I have the table tblPostcodes in sheet "Lists".
postalcodes in column 'U', cities in 'V'. The columns have headers, so data starts on row 2.

I found how to fill cmbPostalcode with the UNIQUE values from that table, but i fail to link the corresponding cities in the cmbCity combobox.

the current code that is not working:
(i dont get errors, cmbCity is just not getting filled)

Private Sub cmbPostalcode_DropButtonClick()
Dim vList, d As Object, i As Long
vList = Sheets("Lists").Range("U2", Sheets("Lists").Cells(Rows.Count, "U").End(xlUp)).Value
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbBinaryCompare
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = 1
    Next
       cmbPostalcode.List = d.keys
       cmbCity.Text = ""
End Sub


Private Sub cmbCity_DropButtonClick()
Dim vList, d As Object, i As Long

 vList = Sheets("Lists").Range("U2", Sheets("Lists").Cells(Rows.Count, "V").End(xlUp)).Value

    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbBinaryCompare
    
    For i = LBound(vList) To UBound(vList)
    If vList(i, 1) = cmbPostalcode.Value Then d(vList(i, 2)) = 1
    Next
    cmbCity.List = d.keys
End Sub

thanks in advance!

 

[Fluff]

There is nothing wrong with the code & it works for me.
Are columns U & V the same length?

 

[MarioExcelNoob]

There is nothing wrong with the code & it works for me.
Are columns U & V the same length?

Hello Fluff

Thanks for the answer.

Yes the columns are the same length (both down to row 2766).
I've been staring at this code and my comboboxes for a typo or something, but can't find anything.

 

[Fluff]

Are your postcodes numerical?

 

[MarioExcelNoob]

Are your postcodes numerical?

Yes, all postcode are 4 numbers long.

 

[Fluff]

In that case try

    If vList(i, 1) = Val(cmbPostalcode.Value) Then d(vList(i, 2)) = 1

All comboBox values are text

 

[MarioExcelNoob]

In that case try

    If vList(i, 1) = Val(cmbPostalcode.Value) Then d(vList(i, 2)) = 1

All comboBox values are text

god... finaly, it works! :D
thank you very much! I would've gone mad over this.
i'll be sleeping good tonight now.

thank you again, have a nice evening!

 

[Fluff]

You're welcome & thanks for the feedback.