Button Code to Alternate Sort Order ?

[Paul Cross]

I've got a range called "Data" and a column called "City" and a button
called SortCity on top of that column. I've recorded a macro that
sorts the column (ASCENDING). How do I adjust the code so that
alternate clicks on the button sort ASCENDING then DESCENDING, etc.

My code thus far:

Sub SortCity()
' SortCity Macro
' Macro recorded 11/12/2004 by pcross
Application.Goto Reference:="Data"
Selection.Sort Key1:=Range("G4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

I tried If / Then logic with a variable (eg assign it 1, then 0, etc.)
and also tried If / Then logic by comparing the first cell with the
last (and ASC sort if it's less than, etc) but in both cases the sort
order didn't change.

Thanks for help,
Paul Cross

 

[Jim Cone]

Paul,

The following seems to work.
Change the A1 cell as desired...
'---------------------------------------------------
Sub SortCity()
Dim SortOrder As Long

With Range("A1")
If .Value < 1 Or .Value > 2 Then .Value = 1
SortOrder = .Value
End With

Application.Goto Reference:="Data"
Selection.Sort Key1:=Range("G4"), Order1:=SortOrder, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Value = (3 - SortOrder)
End Sub
'---------------------------------------------------
Regards,
Jim Cone
San Francisco, CA