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.

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?

--

Dave Peterson

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.

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

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?