MS Excel VBA (two macros under one button)

Hello! Is it possible to put two macros under one button. I made a macro to sort my table, but is it possible to make it so that if I press the button it will sort it A to Z and when I press it again it will sort the table from Z to A.

At the moment my code is:

Sub Klass1_AZ()

Range("start1:end1").Select
Selection.Sort Key1:=Range("start1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

It is a standard Macro recording code.

I hope it can be done!

thanks.

Sure! You can use a 'Static' booolean variable in your procedure to recall which order was last applied; look that word up in your Excel VB help :)

Alternatively, you can change the 'Caption' property of the button each time it is clicked, and test for a word or phrase in that property to tell the procedure which way to sort.

There's also the ToggleButton control which "naturally" alternates between two states. :)

Yeah, but it's not as satisfying as a "real" click :p

Besides, I'd say toggles are generally used to assign a "state" rather than to execute command. But then even Excel uses 2 buttons for the sort orders, so this whole idea is already a little unconventional :D

Besides, I'd say toggles are generally used to assign a "state" rather than to execute command.
Exactly! List.SortOrder = Ascending and List.SortOrder = Descending!!

But yeah, the point about two controls being more intuitive makes a lot of sense to me. One could even use a button and a toggle and we'd both be happy. :p