We help IT Professionals succeed at work.
Get Started

Excel VBA Sort Toggle

Last Modified: 2018-01-17
I have a protected sheet (that emanates from a protected macro-enabled template) that requires the ability to sort.  Even though I have the Sort function checked to allow users to sort, it does not allow it unless the sheet is unprotected for some reason.  I have code that will sort in ascending or descending code, but what I need is a macro that will toggle between the two choices.  So you run it once, it sorts ascending, you run it a second time, it sorts descending, a third time would be ascending, and so on.  I've adapted several different codes as well as trying some of my own, but inevitably it seems to either fail or it doesn't toggle the sort order.

Also, another variable is that I want the sort key to be whichever column the currently selected cell is.  So if the user is in cell A23, the sort would occur based on Column A; if the selected cell is Z432 then the sort would be based on column Z, etc.

Here is the code I'm currently working with:
Private Sub Sorter()

Dim xlSort As XlSortOrder
Dim LastRow As Long
Dim aCell As Range
Dim rng As Range
Dim Loc As Range

Set Loc = Selection

Set aCell = ActiveCell

With ActiveSheet
   LastRow = .Cells(.Rows.Count, Loc).End(xlUp).Row
    Set rng = Range(aCell).Resize(LastRow, 1)

    With rng
        If (.Cells(1).Value > .Cells(LastRow - 1).Value) Then
           xlSort = xlAscending
           xlSort = xlDescending
        End If

        .Sort Key1:=.Cells(1), Order1:=xlSort, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    End With
 End With

End Sub

Open in new window

Here is my initial code that works but only sorts in ascending order, but does so according to whichever column the selected cell resides:
Option Explicit

Sub Sort() 'Ctrl + Shift + S
Dim oneRange As Range
Dim aCell As Range

Set oneRange = Selection
Set aCell = ActiveCell

Application.EnableEvents = False

oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlGuess

    Application.EnableEvents = True
   ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
        True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:= _

    ActiveWorkbook.Protect Structure:=True, Windows:=False

End Sub

Open in new window

Thank you anyone who can help!
Watch Question
Group Finance Manager
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE