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?
Code
Private Sub UserForm_Activate()
'Populate ComboBox1
Dim ws As Worksheet, rCell As Range, Key
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("Quartz")
Me.ComboBox1.Clear
For Each rCell In ws.Range("B4", ws.Cells(Rows.Count, "B").End(xlUp))
If Not Dic.exists(LCase(rCell.Value)) Then
Dic.Add LCase(rCell.Value), Nothing
End If
Next rCell
For Each Key In Dic
Me.ComboBox1.AddItem Key
Next
End Sub
Private Sub ComboBox1_Change()
'Populate ComboBox2
Dim ws As Worksheet, rCell As Range, Key
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("Quartz")
Me.ComboBox2.Clear
For Each rCell In ws.Range("B4", ws.Cells(Rows.Count, "B").End(xlUp))
If rCell.Value = ComboBox1.Value Then
If Not Dic.exists(LCase(rCell.Offset(0, 1).Value)) Then
Dic.Add LCase(rCell.Offset(0, 1).Value), Nothing
End If
End If
Next rCell
For Each Key In Dic
Me.ComboBox2.AddItem Key
Next
End Sub
Display More
TiA,