Help With Sorting Data.

[Da~One]

Hello all.

I have an excel spreadsheet that contains roughly 200 rows and 10 columns
(fields) of data.
On the 10 columns (my fields) I have a row for the column titles at the top
of the worksheet. When the user clicks any of the column titles I would
like to sort (both ascending and descending) on that title.

What I am doing now to do this is as follows: I captured the
"BeforeDoubleClick" and the "BeforeRightClick" events for the worksheet.
The double click does ascending sorting, the right click does the
descending. I make sure the row that was clicked was my title row and then
use the column of the cell that was clicked as my sort key. I then cancel
the click event if the click ocurred on my title row. I am thinking this is
not the best way to do this. Anyone have any better ideas? I am sure this
has been something that one of you has had to do before!

Ideally I would like the user to single click for BOTH sorts (ascending and
descending). if the column clicked is not sorted at all, it will sort
ascending, if it is currently sorted, it will do the opposite of its current
sort (IE, if it is currently sorted ascending, it will sort descending and
vise versa). How can I accomplish it this way? Is there a better way?
Love to hear any ideas out there!

Thank you all for any help you can offer.

[Sam Mitchell]

Public SrtOrd

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("a1:j1")) Is Nothing Then
Application.EnableEvents = False
If SrtOrd <> xlDescending Then
SrtOrd = xlDescending
Else
SrtOrd = xlAscending
End If
Range("a1").CurrentRegion.Sort key1:=Cells(1, Target.Column), _
Order1:=SrtOrd, header:=xlYes
Cells(2, Target.Column).Select
Application.EnableEvents = True
End If

End Sub

"Da~One" <da...@whothat.com> wrote in message
news:ech$Q2s2CH...@TK2MSFTNGP10.phx.gbl...

[Dave Peterson]

John Walkenbach describes a technique of putting a shape in a cell, making it
invisible and using that to catch the single click.

You can read more about it here:

http://j-walk.com/ss/excel/tips/tip12.htm

If that seems like something you'd like, how about these two routines.

The first is a setup procedure--it needs to be run once. It adds a shape to
each cell in a range. (I used B1:K1; modify to match your data.)

Option Explicit
Sub setupOneTime()

Dim myRng As Range
Dim myCell As Range
Dim curWks As Worksheet
Dim myRect As Shape

Set curWks = ActiveSheet

With curWks
'10 columns
Set myRng = .Range("b1").Resize(1, 10)
For Each myCell In myRng.Cells
With myCell
Set myRect = .Parent.Shapes.AddShape _
(Type:=msoShapeRectangle, _
Top:=.Top, Height:=.Height, _
Width:=.Width, Left:=.Left)
End With
With myRect
.OnAction = ThisWorkbook.Name & "!SortTable"
.Fill.Visible = False
.Line.Visible = False
End With
Next myCell
End With
End Sub

The second procedure does the sort based on what rectangle you clicked. I
assumed that column B had data in all the rows in the range. (Make sure you
change this range to match the sub above, too.) If the range is static, you
could even just use that range name.

The procedure just looks at the 2nd cell in that column and compares it to the
last cell in the table (for that column). And bases the sort on what it finds.

Sub sortTable()

Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim LastRow As Long

Set curWks = ActiveSheet
With curWks
myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
Set myTable = .Range("b1:b" & LastRow).Resize(, 10)
If .Cells(myTable.Row + 1, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending
Else
mySortOrder = xlAscending
End If
myTable.Sort key1:=.Cells(myTable.Row, myColToSort), _
order1:=mySortOrder, header:=xlYes
End With

End Sub

--

Dave Peterson
ec3...@msn.com

[Dave Peterson]

And if you before you run the setup proc a second time, you'll want to delete
the existing rectangles.

If you don't have any other objects on the worksheet, you can:
Edit|Goto|Special|objects
Hit the delete key

If you have shapes you want to keep, you can show the drawing toolbar and use
the arrow to lasso the rectangles that you want to delete, then hit the delete
key.

--

Dave Peterson
ec3...@msn.com