Items in combobox added with condition of another combobox

[tiger837]

Hi everyone,

I have a problem for a userform which uses comboboxes.
So basically I have column A with "area" items and column B with "process" items. I don't know how many different items I have in theses columns. But everytime you have an area, you have a process written next to it.
I want a userform with a first combobox that will allow us to choose a area written in column A. Even if the area is named multipled times, it will be displayed only once on the combobox. Furthermore, all areas in the combobox are sorted A->z.
I have written this code that is working, but I can't sort the areas a->Z.

Private Sub Userform_Initialize()
    Dim j As Integer
    
    For j = 4 To Range("A65536").End(xlUp).Row
        AreaBox = Range("A" & j)
        If AreaBox.ListIndex = -1 Then AreaBox.AddItem Range("A" & j)
    Next j
    
End Sub

After that, I want a 2nd combobox that will list the process items that exist for the area choosen. Like for the first combobox, each process items will appeared only once, and sorted a->z.
It's for this combobox that I have more trouble because with the following combobox, all processes appeared even if they don't exist for the selected area.

Private Sub AreaBox_change()


    Dim r As Range
    Dim c As Range
    
    Set r = Range("A4:A200")


    For Each c In r
        If c.Value = AreaBox.Value Then
            ProcessBox.AddItem c.Offset(0, 1).Value
            If ProcessBox.ListIndex = -1 Then ProcessBox.AddItem c.Offset(0, 1)
        End If
    Next c


End Sub

If anyone have a solution for theses problems, I'll be glad to ear it ! Thanks

 

[Fluff]

You're welcome & thanks for the feedback