1 click sorting *almost there*

  • Thread starter Thread starter m3s3lf
  • Start date Start date
M

m3s3lf

I've got some invisible rectanges that sit on top of my headers, so when
somebody clicks on them, they sort based on that column. (All I did was
assign some simple macros to each rectangle.) Anyhow, I would really
like to be able to sort ascending with 1 click and descending with
another click. Does anybody have any ideas on how to accomplish this?

Here's one of my sort macros:

Code:
 
Hi,

Maybe this site will help you:
www.oaltd.co.uk

and try to download quckshort.zip ,
there's algorithm sample using API

Rgds,

HAlim

m3s3lf menuliskan:
Click to expand...

That's very impressive... but way over my head! I'm only looking for a
couple lines of code. Can anybody else help?
Thank you!
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:D1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name <> "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, so put it in the sheet's code module.
Author unknown

Mike F
 
Well, I had just got it working when I saw your post. Thanks to
everyone for the help. This is how I ended up making it work:

I changed this:

Code:
--------------------
Sub ArchiveSortBySalePrice()
Range("ClosedSales").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
--------------------


To this:

Code:
--------------------

Sub ArchiveSortBySalePrice()
Dim SortOrder As String
EndRow = Range("D65536").End(xlUp)
If Range("D3") >= EndRow Then
SortOrder = xlAscending
ElseIf Range("D3") < EndRow Then
SortOrder = xlDescending
End If
Range("ClosedSales").Sort Key1:=Range("D3"), Order1:=SortOrder, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Well, that last bit of code I posted is working perfectly.... -unless
row 3 is hidden, then it only sorts 1 way, not ascending -and
descending. I need to tell it how to compare the top _visible_ row o
data with the bottom _visible_ row of data to determine the sort order
My problem is that there is a header at Row 2, so I can't use th
End.(xlDown) trick. Anybody have any ideas?

Thanks a lot,
Bill
 
m3s3lf said:
Thanks for the reply, but that code still doesn't work if the top row is
hidden. Anybody else?
TIA!Hi,
Click to expand...

Maybe you could unhide the row at the begining of your code then hide
it again at the end
IE:
Unhide row
Run code
Hide row
 
Desert said:
Hi,

Maybe you could unhide the row at the begining of your code then hide
it again at the end
IE:
Unhide row
Run code
Hide row
Click to expand...

Unfortunately, it's just not possible. I think what I need to do is
start at row 4, check to see if it's hidden, if it is, go to row 5,
check that, etc... and then set the first row that is not hidden as
"TopRow" or something... it's just taking me days to figure out the
code.
Thanks!
 
Back
Top