Populating ComboBox with unique values (case sensitivity)

  • I have the below code to populate ComboBox1 on UserForm activation, and ComboBox2 on ComboBox1 change.


    The referenced Range (Column B) has names in propercase, but ComboBox1 populates in all lowercase, so ComboBox2 never populates because the lowercase ComboBox1 value doesn't match the range content in propercase.


    How do I get ComboBox1 to populate identical to the range content?



    TiA,

  • Re: Populating ComboBox with unique values (case sensitivity)


    Never mind... the answer is right in front of me... LCase... get rid of it... Duh...


    Working code;

  • Re: Populating ComboBox with unique values (case sensitivity)


    an alternative solution... :)


    [vb]Option Explicit


    Private Dic As Object


    Private Sub UserForm_Activate()

    'Populate ComboBox1
    Dim ws As Worksheet, v, i As Long
    Dim sKey As String, sItems As String

    Set Dic = CreateObject("Scripting.Dictionary")
    Set ws = Worksheets("Quartz")

    v = ws.Range("B4", ws.Cells(Rows.Count, "B").End(xlUp).Offset(, 1)).Value

    Me.ComboBox1.Clear

    For i = 1 To UBound(v, 1)
    sKey = v(i, 1)
    If Len(sKey) Then
    sItems = Dic.Item(sKey)
    If Len(sItems) Then
    If InStr(1, sItems, v(i, 2), 1) = 0 Then
    Dic.Item(sKey) = sItems & "|" & v(i, 2)
    End If
    Else
    Dic.Item(sKey) = v(i, 2)
    End If
    End If
    Next

    If Dic.Count Then
    Me.ComboBox1.List = Dic.keys
    End If

    End Sub

    Private Sub ComboBox1_Change()

    'Populate ComboBox2
    Dim v, sKey As String

    Me.ComboBox2.Clear

    If Me.ComboBox1.ListIndex = -1 Then Exit Sub

    sKey = ComboBox1.Value
    v = Split(Dic.Item(sKey), "|")

    Me.ComboBox2.List = v


    End Sub[/vb]

  • Re: Populating ComboBox with unique values (case sensitivity)


    Thanks for the input, Kris... it's always good to see other ways of skinning the proverbial cat (no offense to cats owners).