Can I set up a sort by clicking on table a heading

[Guest]

I have a table I would like to set up to be sorted by clicking on a table
heading. Is that possible in Excel?


Thank you.

 

[Roger Govier]

Hi Aaron

Dave Peters has written some nice code to do this. It is available for
download at Debra Dalgleish's site
http://www.contextures.com/xlSort02.html#Rectangles

Regards

Roger Govier

 

[Guest]

Perfect!!

Thank you.

 

[Otto Moehrbach]

Aaron
Yes. I take it that you want the sort key to be the column you clicked
on. You would use the Worksheet_SelectionChange event macro. You would
write code in that macro to check that the cell selected is one of the
header cells. If it isn't, do nothing. If it is, sort the table with that
column as the key column. An example macro follows. I assumed the table
had the range name "TheTable" and that the range of the headers is A1:E1.
Make the necessary changes. Post back if you need more. HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:E1")) Is Nothing Then
Range("TheTable").Sort Key1:=Target.Offset(1), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

 

[Roger Govier]

Well, thank Dave Peterson actually.

Apologies Dave for shortening your name in the previous post, Dave Peters
was a guy I was at University with.

Regards

Roger Govier

 

[Dave Peterson]

I'm sure he as a very nice person--even though he was missing that "ON"!