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("B2:D10")
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


"Ting" wrote:

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?