troubleshooting Question

Excel VBA Sort Toggle

Avatar of Randall Clark
Randall Clark asked on
VBAMicrosoft ExcelMicrosoft Office
5 Comments1 Solution446 ViewsLast Modified:
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

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

Thank you anyone who can help!
Roy Cox
Group Finance Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros