Click event sort ascending then descending

  • Thread starter Thread starter Ting
  • Start date Start date
T

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?
 
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.
 
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.
 
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
 
Back
Top