Sort from Combobox

[mucah!t]

Hello all,

How can I sort a range starting from B6:V6 based on collumn D from a Combobox.

I'd like the the combobox to show all unique names in collumn D so the user can sort the range by selecting one of these names from the Combobox.

Possible?

 

[mucah!t]

I already found a solution to fill the combobox with unique values:

Private Sub Worksheet_Activate()
    Dim c As Range, Coll As New Collection
    On Error Resume Next
    For Each c In Sheets("Urenstaat").[d6:d1000]
        Coll.Add c.Value, c.Value
    Next c
    On Error GoTo 0
    For Each Item In Coll
        Sheets("Urenstaat").ComboBox1.AddItem Item
    Next Item
End Sub

Now I need a way to sort the sheet by selecting one of the items in the combobox.

Ideas?

 

[mucah!t]

With the following code I managed to run a macro from the selection

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
    Case "Mike"
       Call Mike
    Case "John"
       Call John
    Case "Patrick"
       Call Patrick
       
 End Select
End Sub

The macro's look something like this:

Sub Mike()
    Range("D9").Select
    Selection.AutoFilter Field:=3, Criteria1:="Mike"
End Sub

It's now working properly.

 

[mucah!t]

Still one question.

How can I add an extra item to the list, that isn't in the range?
It should be shown as first item, called "All"

Private Sub Worksheet_Activate()
    Dim c As Range, Coll As New Collection
    On Error Resume Next
    For Each c In Sheets("Urenstaat").[d6:d1000]
        Coll.Add c.Value, c.Value
    Next c
    On Error GoTo 0
    For Each Item In Coll
        Sheets("Urenstaat").ComboBox1.AddItem Item
    Next Item
End Sub