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 XlSortOrderDim LastRow As LongDim aCell As RangeDim rng As RangeDim Loc As RangeSet Loc = SelectionSet aCell = ActiveCellWith 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 WithEnd Sub
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 ExplicitPrivate 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 " & sSortEnd Sub