Switch between Ascending and Descending order by one macro [VBA]

[Martin_H]

Hi,

I use this macro (see below) to sort the data in ascending order in the table.

My macro is assigned to the cutom MSO button.

I'd like to transfer my macro to the state which sorts the data in ascending order on the first click, sorts the data in descending order on the second click and again in ascending order on the third click, and so on.

Basically, I want to switch between ascending and descending order by using one macro (one button).

Thank you. Much appreciated.

Public Sub Ascending_Order(ByRef control As Office.IRibbonControl)

With Application
.ScreenUpdating = False

Dim LastCell As Range

With ActiveSheet
Set LastCell = .Cells(Rows.Count, 2).End(xlUp)
.Range("D3", LastCell).sort Key1:=.Range("D3"), Order1:=xlAscending, Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
End With

With Range("B3").Font
.Color = vbGreen: .Bold = True
End With

With Range("U3,N3,O3,P3,Q3.F3").Font
.Color = vbWhite: .Bold = False
End With

.ScreenUpdating = True
End With

End Sub

 

[Sektor]

Public Sub Ascending_Order(ByRef control As Office.IRibbonControl)
    Static x As Boolean '// For swapping state
    Dim sort_order As XlSortOrder
 
    With Application
        .ScreenUpdating = False
     
        Dim LastCell As Range
     
        With ActiveSheet
            Set LastCell = .Cells(Rows.Count, 2).End(xlUp)
            sort_order = IIf(x, xlDescending, xlAscending) '//Select order
            x = Not x                                      '//Swap state
            .Range("D3", LastCell).Sort Key1:=.Range("D3"), Order1:=sort_order , Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
        End With
     
        With Range("B3").Font
            .Color = vbGreen: .Bold = True
        End With
     
        With Range("U3,N3,O3,P3,Q3.F3").Font
            .Color = vbWhite: .Bold = False
        End With
     
        .ScreenUpdating = True
    End With

End Sub

 

[Akuini]

Maybe something like this:

Dim flag As Boolean

Sub to_sort()
Dim tx As Variant

    flag = Not flag
    If flag Then tx = xlAscending Else tx = xlDescending
    Range("A1:A10").Sort Key1:=Range("A1"), Order1:=tx, Header:=xlYes

End Sub

 

[Martin_H]

Thank you Sektor & Akuini.

Much appreciated.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.