XL2002 - Sort xlAscedending / xlDescending

[Trevor Williams]

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

 

[Jim Thomlinson]

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

 

[Dave Peterson]

You may want to look at this:
http://contextures.com/xlSort02.html
From Debra Dalgleish's site.

 

[Trevor Williams]

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

 

[Trevor Williams]

Hi Dave. I've downloaded the file from Debra's site and will check it out.
I like the idea of the invisible rectangles!

Trevor

 

[Jim Thomlinson]

FYI the code I posted does not rely on the sort values to be numbers. It
works equally well with text.

 

[Trevor Williams]

Hi Jim

I have a list of Y / N responses in one column and a list on Months
(non-abbreviated e.g. January) in another column.
I re-tried your code and it works a couple of times then stops.
Can you test it using the above scenario and let me know if it works
everytime for you? I'd like to use your version as it's so much neater than
mine!

Also, another question regarding my list on Months. I've set up validation
for the user to choose January - December. I need to sort it from January to
December but using the Sort function lists it alphabetically
(April-September). To get around it I have code that works out the number
of the month in another column and sorts that column instead. (the month
column is set as 'general', not 'date')

My code is a bit cumbersome, and wondered if you have a better solution?

For Each cell In rng
If cell = "January" Then
cell.Offset(0, -1) = 1
ElseIf cell = "February" Then
cell.Offset(0, -1) = 2
ElseIf cell = "March" Then
cell.Offset(0, -1) = 3

'...etc

Else
cell.Offset(0, -1) = ""
End If
Next