Solved

VBA - Toggle between Ascending and Descending Sort

Posted on 2014-01-23
4
591 Views
Last Modified: 2014-01-23
See attached.

Note that if you click on A2, A3,A4 or A5 the sheet sorts ASCENDINGLY left to right.

I need to toggle this from Ascends to Descends (and vice versa).

See my VBA - How do I toggle?
SortLeftToRight.xlsm
0
Comment
Question by:Patrick O'Dea
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
brendanmeyer earned 500 total points
ID: 39805268
update the code to

Option Explicit

Public bsort As Boolean
Public lastrow As Integer

Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sortzone As Range, thisSheet As Worksheet
    If Target.Column = 1 And Target.Row > 1 Then
        Set thisSheet = ActiveWorkbook.ActiveSheet
        Set sortzone = thisSheet.Range("A1").CurrentRegion.Offset(0, 1)
        Set sortzone = sortzone.Resize(, sortzone.Columns.Count - 1)
        
        thisSheet.sort.SortFields.Clear
        If lastrow = Target.Row Then
            bsort = Not bsort
        Else
            lastrow = Target.Row
            bsort = True
        End If
        If bsort = True Then
            thisSheet.sort.SortFields.Add Key:=Target.Offset(0, 1).Resize(, sortzone.Columns.Count), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Else
            thisSheet.sort.SortFields.Add Key:=Target.Offset(0, 1).Resize(, sortzone.Columns.Count), _
                SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        End If
        With thisSheet.sort
            .SetRange sortzone
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlLeftToRight
            .SortMethod = xlPinYin
            .Apply
        End With
        Cancel = True
    End If
    Range("A1").Select
End Sub

Open in new window

0
 

Author Comment

by:Patrick O'Dea
ID: 39805286
One word.... perfect!

Thanks!
0
 
LVL 14

Expert Comment

by:brendanmeyer
ID: 39805307
No Problem, remember to accept the answer :)
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39805311
Perfect!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now