Sorting of table depending on a drop-down-list entry

[andreashermle]

Dear Experts:

I got a drop-down combo box with three entries (DE, EN, ES). As soon
as the user selects one of these entries a table on the same worksheet
has to be sorted by the entry chosen.

Example:
Combobox entries are: DE, EN, ES

The list to be sorted has the following make-up

PRODUCT COUNTRY
Item 1 DE
Item 2 ES
Item 3 DE
Item 3 EN
Item 5 ES

Task: as soon as the user selects a value of the drop-down list, the
table (on the same worksheet: A1:B6) is to be sorted by that field
value (using VBA).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 

[Bernie Deitrick]

Andreas,

You can't actually sort by an item - you sort by the entire list, and you can only sort ascending or
descending or by a specific order that you create using custom lists.

To do what you want, you would need to add a column of formulas to return a value that will sort the
way that you want. Something like

=IF(B2=$C$1,1,0)

copied down to match your list, where C1 will contain the value from the combobox. Then you could
sort the data table based on that column, in descending order.

But perhaps what you really want is to filter the table to show just the rows where the country
value equals the combo box value.

Of course, the code to do those two different actions differs, so post back with what you actually
want.

HTH,
Bernie
MS Excel MVP

 

[andreas-hermle]

Andreas,

You can't actually sort by an item - you sort by the entire list, and youcan only sort ascending or
descending or by a specific order that you create using custom lists.

To do what you want, you would need to add a column of formulas to returna value that will sort the
way that you want.  Something like

=IF(B2=$C$1,1,0)

copied down to match your list, where C1 will contain the value from the combobox.  Then you could
sort the data table based on that column, in descending order.

But perhaps what you really want is to filter the table to show just the rows where the country
value equals the combo box value.

Of course, the code to do those two different actions differs, so post back with what you actually
want.

HTH,
Bernie
MS Excel MVP













- Show quoted text -

Dear Bernie,

ooops, english being not my mother tongue I sometimes make mistakes.
Of course 'filtering the list' is meant and not 'sorting', i.e. I
would like to filter the list for the value that was selected from the
combo box.

Thank you in advance for your help. Regards, Andreas

 

[Bernie Deitrick]

Andreas,

Copy the code below, right-click the sheet tab, select "View Code" (whatever the equivalent in your
Excel), then paste the code into the window that appears.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("D413").AutoFilter Field:=1, Criteria1:=Range("A1").Value
End Sub


Change the $A$1 and A1 to the address of the cell that you are using to select the country code, and
change the D413 to the address of the list of countries - with the D4 being the title cell for the
column.

If the list can grow longer, you could change the code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Raange(Range("D4", Range("D4").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("A1").Value
End Sub


--
HTH,
Bernie
MS Excel MVP

Andreas,

You can't actually sort by an item - you sort by the entire list, and you can only sort ascending
or
descending or by a specific order that you create using custom lists.

To do what you want, you would need to add a column of formulas to return a value that will sort
the
way that you want. Something like

=IF(B2=$C$1,1,0)

copied down to match your list, where C1 will contain the value from the combobox. Then you could
sort the data table based on that column, in descending order.

But perhaps what you really want is to filter the table to show just the rows where the country
value equals the combo box value.

Of course, the code to do those two different actions differs, so post back with what you actually
want.

HTH,
Bernie
MS Excel MVP













- Show quoted text -

Dear Bernie,

ooops, english being not my mother tongue I sometimes make mistakes.
Of course 'filtering the list' is meant and not 'sorting', i.e. I
would like to filter the list for the value that was selected from the
combo box.

Thank you in advance for your help. Regards, Andreas

 

[andreas-hermle]

Andreas,

Copy the code below, right-click the sheet tab, select "View Code" (whatever the equivalent in your
Excel), then paste the code into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("D413").AutoFilter Field:=1, Criteria1:=Range("A1").Value
End Sub

Change the $A$1 and A1 to the address of the cell that you are using to select the country code, and
change the D413 to the address of the list of countries - with the D4 being the title cell for the
column.

If the list can grow longer, you could change the code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Raange(Range("D4", Range("D4").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("A1").Value
End Sub

--
HTH,
Bernie
MS Excel MVP











Dear Bernie,

ooops, english being not my mother tongue I sometimes make mistakes.
Of course 'filtering the list' is meant and not 'sorting', i.e. I
would like to filter the list for the value that was selected from the
combo box.

Thank you in advance for your help. Regards, Andreas- Hide quoted text -

- Show quoted text -

Dear Bearnie,

great. It is working as desired. Thank you very much for your
professional help. Regards, Andreas