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

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
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,

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 -
Click to expand...

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,

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("D4:D13").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 D4:D13 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 -
Click to expand...

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,

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("D4:D13").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 D4:D13 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 -
Click to expand...


Dear Bearnie,

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