Excel column sort with single click

[Da~One]

Hello folks:

I have an Excel 2000 spreadsheet with various data that I need to sort by
column. Essentially, I would like the user to be able to click the column
title and have all the data below sorted (ascending) on that column that the
title is in. I can do this easily in VBA. The problem? I can't use VBA!
Is there a solution? Are there any alternates I can use to solve my
problem?

Thank you for your time.

[Bonnie Keithley]

To avoid having your column titles (First Name, Last Name, etc) sorted, you
will first have to "hide" the row(s) with the titles. THEN:

Position your cursor anywhere in the column you want sorted
Click on the "A to Z" button on the tool bar

Don't forget to unhide your titles.

Of course, if you don't mind using more than one click to sort, there is
always the Data | Sort option where you can set multiple sort criteria and
indicate whether or not you have header rows. Then you don't have to hide
and unhide.

Let me know if this helps --------------- Bonnie

"Da~One" <da...@whothat.com> wrote in message
news:ugLaqvMI...@tk2msftngp13.phx.gbl...

[Dave Peterson]

Excel doesn't have a single click on a worksheet cell event. You could use
doubleclick or rightclick or something else.

How about putting rectangles over your headers. Then you could assign a macro
to those rectangles that sorts by that column.

Here are two macros. The first one is a run once and throw away. It puts a
rectangle in A1:J1 (10 headers??). The second is assigned to all the
rectangles. It does the real work. I used column A to determine number of rows
in the table. (still 10 columns). You click on the rectangle, it sorts
ascending. Click again, it sorts descending.

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("a1").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
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, "a").End(xlUp).Row
Set myTable = .Range("a1:a" & 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