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