1 click sorting *almost there*

[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:

Please Login or Register  to view this content.

Thank you!!

[[email protected]]

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

[m3s3lf]

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!

[Mike Fogleman]

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" <[email protected]> wrote in
message news:[email protected]...
>
> [email protected] 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
>

[m3s3lf]

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:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Thanks again!

[m3s3lf]

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?

Thanks a lot,
Billy

[Debra Dalgleish]

Dave Peterson has sample code here:

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

[m3s3lf]

Dave Peterson has sample code here:

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!

[Desert Piranha]

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

[m3s3lf]

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]

I can't seem to get any of this code to work
LiNK