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:
--------------------
Sub ArchiveSortBySalePrice()
Range("ClosedSales").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
--------------------


Thank you!!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=564975

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:
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:
--------------------
Sub ArchiveSortBySalePrice()
Range("ClosedSales").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Wrote:
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:
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:
--------------------
Sub ArchiveSortBySalePrice()
Range("ClosedSales").Sort Key1:=Range("D2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

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!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=564975

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
"m3s3lf" wrote in
message ...

Wrote:
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:
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:
--------------------
Sub ArchiveSortBySalePrice()
Range("ClosedSales").Sort Key1:=Range("D2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

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!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile:
http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=564975

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

--------------------


Thanks again!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=564975

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

--
m3s3l
-----------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...fo&userid=3487
View this thread: http://www.excelforum.com/showthread.php?threadid=56497

Dave Peterson has sample code he

http://www.contextures.com/xlSort02.html

m3s3lf wrote:
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 of
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 the
End.(xlDown) trick. Anybody have any ideas?

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra Dalgleish Wrote:
Dave Peterson has sample code he

http://www.contextures.com/xlSort02.html

m3s3lf wrote:
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
of
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 the
End.(xlDown) trick. Anybody have any ideas?

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html


Thanks for the reply, but that code still doesn't work if the top row
is hidden. Anybody else?
TIA!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=564975

m3s3lf Wrote:
Thanks for the reply, but that code still doesn't work if the top row is
hidden. Anybody else?
TIA!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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=564975

Desert Piranha Wrote:
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

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!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=564975