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
Thanks,
AB
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
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
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.
ASKER
Xanius,
Thanks (again) for adding back to my original post
Art
Thanks (again) for adding back to my original post
Art
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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(
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
:=xlSortNormal
Case Is = 2
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
:=xlSortNormal
End Select
End If
End Sub