Link to home
Start Free TrialLog in
Avatar of ArtBarnwell
ArtBarnwell

asked on

Excel 2000 – Sorting data by clicking on column title cells

I need a  sort solution where users can click on column labels and have Excel  sort data based on the label selected.   In some cases I will need the data sorted by 2 or 3 columns when certain labels are ”clicked”.  For instance, is a user were to click on the label “Last Name”, the worksheet would  sort by the “Last Name” and “First Name” columns;  Clicking on  “State” would sort by  data in the “State” and ”City” columns;  Clicking on “Company Name” would sort by data in the “Company Name and “Contact Columns.

Thanks,
AB
Avatar of Jeggburt
Jeggburt
Flag of United Kingdom of Great Britain and Northern Ireland image

I've only completed this for the first two columns of data where column A is "Last Name" and column B is "First Name".

You can add more for the other columns as you wish.

The following should be put into your worksheet_selectionchange procedure:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim LastRow As Long
   
    LastRow = Cells(65536, 1).End(xlUp).Row
    'gets last row of populated data

    If (Target.Row = 1) Then
        Select Case Target.Column
            Case Is = 1
                Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
            Case Is = 2
                Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
        End Select
    End If

End Sub
Art,

I know what you want and I'm working on it - don't close until you have had a chance to see my goods.

Kevin
Avatar of xanius
xanius

This question was preceed by  https://www.experts-exchange.com/questions/22551806/Excel-2000-Prevent-column-deletion-without-worksheet-protection.html for anyone who wnats to catch up with the whole story.
Avatar of ArtBarnwell

ASKER

Xanius,
Thanks (again) for adding back to my original post
Art
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi All,
I apologize for taking so long to respond but I have been out-of-town.

Kevin’s solution is an outstanding alternative and is much easier for users to use.  Most notably, the persistent visual indicators (Arrows) allow you to always know exactly how the sheet is sorted.  Very slick work.

I want personally thank Kevin for going the extra mile here.  I didn’t simply provide help but took the time to provide a professionally written and fully functional “turn-key” solution (again).  I highly recommend that you download Kevin’s Sorting\Filtering solution while you can.  The link is http://www.zorvek.com/downloads/Sort-and-Filter-Example.xls

Thanks Again,
Art