Hi Jim

Thanks for the response. I was nearly there with my 'dabblings'.
I reverted back to using my range value checking to see if it should be
Ascending or Descending as it's not just numbers in the lists, and your code
tests to see if cell 1 is < than cell 2...

Thanks again.

Trevor

"Jim Thomlinson" wrote:

Give this a try...

Sub SortEachCol()
Dim shp As Shape
Dim lngSortOrder As Long
Dim mycol As Long

Set shp = ActiveSheet.Shapes(Application.Caller)
mycol = shp.TopLeftCell.Column

If Cells(13, mycol).Value Cells(14, mycol).Value Then
lngSortOrder = xlAscending
Else
lngSortOrder = xlDescending
End If

'this sorts the list - I need to chnge the Range("C:C") to Range(myCol)
'and set Order1:=myOrder
Range("C12:G24").Sort Key1:=Cells(12, mycol), Order1:=lngSortOrder, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
--
HTH...

Jim Thomlinson


"Trevor Williams" wrote:

Dear All

I have a simple table (with a header row) containing 5 columns - 3 of the
columns will need to be sorted by the user.
Above each of the columns that need sorting there is a shape that is used to
run the sorting macro.

Here's what I'd like to do.
1 - Rather than creating 3 macros to sort 3 columns I'd like to create one
that runs depending on which button, (or shape in this case), is selected.

2 - Depending on the current order of the sorted list, I'd like the macro to
do the opposite - i.e. if the list is currently xlAscending, next time the
button is pressed for that list, it sorts in xlDescending.

Code below - any help appreciated

Trevor Williams.
--------
Sub SortEachCol()
Dim shp As Shape
Set shp = ActiveSheet.Shapes(Application.Caller)
myCol = shp.TopLeftCell.Column 'this returns a column number, not letter

'this checks to see last sort order and sets myOrder to the opposite order
If Range("L13") = "xlAscending" Then
myOrder = "xlDescending"
Else
myOrder = "xlAscending"
End If

'this sorts the list - I need to chnge the Range("C:C") to Range(myCol)
'and set Order1:=myOrder
Range("C12:G24").Sort Key1:=Range("C:C"), Order1:=xlDescending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal
'this updates the new sort order
Range("L13") = myOrder
End Sub