Switch sort direction each time macro is invoked

[halooti]

Hello all

I created a button with excel and assigned a macro to it which I recorded with excel Macro Record..

The button sorts column C ascending, this is the code

[CODE=vb]Sub Macro1()
Range("C7:C11") .Select
Selection.Sort Key1:=Range("C7 "), Order1:=xlAscen ding, Header:=xlGuess ,
OrderCustom:=1, MatchCase:=Fals e, Orientation:=xl TopToBottom, _
DataOption1:=xl SortNormal
Range("D9").Sel ect
End Sub[/CODE]

I want to make this macro sorts Descending when I click on the button again ..
and the Ascending with another click and so on ..

If anyone knows how to do it .. please write the code ..

thanks

[ubentook]

[CODE=vb]Sub Macro1_R1()
Dim lngWay As Long
If Range("C11").Va lue > Range("C7").Val ue Then
lngWay = xlDescending
Else
lngWay = xlAscending
End If
Range("C7:C11") .Sort Key1:=Range("C7 "), Order1:=lngWay, _
Header:=xlGuess , OrderCustom:=1, MatchCase:=Fals e, _
Orientation:=xl TopToBottom, DataOption1:=xl SortNormal
Range("D9").Sel ect
End Sub[/CODE]

Hello all

I created a button with excel and assigned a macro to it which I recorded with excel Macro Record..

The button sorts column C ascending, this is the code

Sub Macro1()
Range("C7:C11") .Select
Selection.Sort Key1:=Range("C7 "), Order1:=xlAscen ding, Header:=xlGuess ,
OrderCustom:=1, MatchCase:=Fals e, Orientation:=xl TopToBottom, _
DataOption1:=xl SortNormal
Range("D9").Sel ect
End Sub

I want to make this macro sorts Descending when I click on the button again ..
and the Ascending with another click and so on ..

If anyone knows how to do it .. please write the code ..

thanks

[halooti]

Sub Macro1_R1()
Dim lngWay As Long
If Range("C11").Va lue > Range("C7").Val ue Then
lngWay = xlDescending
Else
lngWay = xlAscending
End If
Range("C7:C11") .Sort Key1:=Range("C7 "), Order1:=lngWay, _
Header:=xlGuess , OrderCustom:=1, MatchCase:=Fals e, _
Orientation:=xl TopToBottom, DataOption1:=xl SortNormal
Range("D9").Sel ect
End Sub

THANKS UBENTOOK !!!!

[halooti]

I want to use this sub with a different column and i want to know what's the function of this so that i can change it to suit the new table :

Range("D9").Sel ect

[kadghar]

I want to use this sub with a different column and i want to know what's the function of this so that i can change it to suit the new table :

Range("D9").Sel ect

Use WORKBOOKS(index ), WORKSHEETS(inde x) and CELLS(row, col), if you dont specify one of the first two, it'll use the active one.

e.g.

workbooks(1).wo rksheets(2).cel ls(9,4) will refer to the cell D9.

You can put them into a With to make things easier, and use ranges with cells:

[CODE=vb]with workbooks(1).wo rksheets(2)
range(.cells(9, 4), .cells(15,5)) = "hi"
end with[/CODE]
or use the name of the book/sheet/range, instead of the index number:

workbooks("mybo ok").worksheets ("sheet1").rang e("B9:F17")

and this names are strings, so you can modify them as any other string.

HTH

[ubentook]

Range("D9").Sel ect is not required; you can delete it.
'--
The code sorts in Column C.
To have the code work on a different column then...
where one or more upper case C's follows the word Range, change C to the letter of the new column.
'--

I want to use this sub with a different column and i want to know what's the function of this so that i can change it to suit the new table :

Range("D9").Sel ect