Sorting function button

[Guest]

Hi All,

I have three columns, (two text colums, and one numbers column). I can
click on the top cell of any of the three columns and then click on the Sort
Ascending button to sort the list and the entire worksheet will be sorted
with respect to the column I selected.

However, I want to be able to use a clickable color button as the first cell
of each of these columns. When the user clicks on this button it should sort
the entire worksheet by the column below automatically. Is there a way to do
this?

 

[Dave Peterson]

I saved from a previous post:

How about this. You put some rectangles over each of the header cells in row
1. Make them invisible. When the user clicks on one of those rectangles, your
data gets sorted by that column (click on the same rectangle, it sorts in the
opposite direction).

If that sounds promising...

Saved from a previous post...

I put rectangles over the headers (made the borders invisible) so that when you
clicked on the rectangle, it looked like you were clicking on the header.

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

===
The setuponetime routine puts the rectangles in 10 columns A1:J1 with this:
'10 columns
Set myRng = .Range("a1").Resize(1, 10)

The sortTable routine sorts that same table based on the number of cells used in
column A (and 10 columns wide) with this line:

Set myTable = .Range("a1:a" & LastRow).Resize(, 10)

If you have more or less columns, adjust both .resize(,10) portions.

If you have to use a different column to get the last row, adjust this line:
LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm