Click event sort ascending then descending

[Ting]

I have a macro that sorts ascending when first clicked. Is there a way to
sort descending automatically if same button clicked a second time. I tried
to set a global variable to track what was used last time, but received an
error. The only way I can think of is to write to a cell in the spreadsheet
the last sort state.

Am I on the right track?

 

[Dave Peterson]

How about if you just look at the topmost cell and the bottommost cell of the
column that should be sorted.

If the top > bottom, sort in descending order. If the bottom > top, sort in
ascending order.

 

[BigJimmer]

Using a toggle button, you can do this, and it gives you a chancce to display
text on the button that informs the user whether the data is currently sorted
in acsending or descending order.

 

[JLGWhiz]

This is along the line that Dave suggested, but uses a cell on the worksheet
to evaluate the sort order.

Private Sub CommandButton1_Click()
If LCase(Range("A1")) = "descending" Then
Range("A1") = "Ascending"
Else
Range("A1") = "Descending"
End If
With Worksheets(1).Range("B210")
If Range("A1") = "Ascending" Then
.Sort Key1:=Cells(2, 2), Order1:=xlAscending
Else
.Sort Key1:=Cells(2, 2), Order1:=xlDescending
End If
End With
End Sub