Click event sort ascending then descending

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?
 
D

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.
 
B

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.
 
J

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("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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top