asked on

Excel VBA Sort Toggle

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
        Else
           xlSort = xlDescending
        End If

        .Sort Key1:=.Cells(1), Order1:=xlSort, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    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

ActiveWorkbook.Unprotect
ActiveSheet.Unprotect
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:= _
        True

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

End Sub

Open in new window

Thank you anyone who can help!

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randall Clark

ASKER

Just sent you a private message.

Roy Cox

As you are using a Table I have made some changes and corrected some problems with the original code.

The sorting completed message can be deleted if you don't need it.

Option Explicit
Private Sub Sorter()
    Dim oTbl As ListObject
    Dim sSort As String
    Dim xlSort As XlSortOrder
    Dim LastRow As Long
    Dim aCell As Range

    Set aCell = ActiveCell

    If aCell.Count > 1 Then
        MsgBox "Please select only one cell", vbCritical, "Invalid selection"
        Exit Sub
    End If

    With ActiveSheet

        Set oTbl = ActiveSheet.ListObjects(1)
        LastRow = oTbl.ListRows.Count

        With oTbl.DataBodyRange
            If (.Cells(1, aCell.Column).Value > .Cells(LastRow, aCell.Column).Value) Then
                xlSort = xlAscending
                sSort = "Ascending"
            Else
                xlSort = xlDescending
                sSort = "Descending"
            End If

            .Sort Key1:=aCell, Order1:=xlSort, Header:=xlGuess, _
                  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                  DataOption1:=xlSortNormal

        End With
    End With
    MsgBox "Data sorted " & sSort
End Sub

Open in new window

Randall Clark

ASKER

Thank you so much Roy! You're great!

Roy Cox

Pleased to help