Macro Button to sort when pressed

[gmazza76]

Good morning

I have written 2 pieces of code to align a column in my pivot table by highest / lowest values.
Instead of having 2 buttons (1 for each) is there anyway to adjust so each time the button is pressed it alternates between each value?
Is there a way to join both pieces of code into 1 button?

Private Sub CommandButton8_Click()
Range("N8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Agent Name").AutoSort _
        xlAscending, "Avg Talk Time", ActiveSheet.PivotTables("PivotTable1"). _
        PivotColumnAxis.PivotLines(3), 1
End Sub

Private Sub CommandButton9_Click()
Range("O8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Agent Name").AutoSort _
        xlDescending, "Avg Work Time", ActiveSheet.PivotTables("PivotTable1"). _
        PivotColumnAxis.PivotLines(5), 1
End Sub

thanks in advance

 

[Micron]

I have tried to find out how to get the sort property of a range without much luck. That would be my go to solution if I could.
Otherwise you could have a module level variable (strSort) and one button. If strSort is null then sort ascending and set strSort to "Ascending". On 2nd click, test strSort and if not null, do the opposite sort of what the variable value is. The drawback to that is the data might get sorted and saved one way or the other. If that happens to be ascending then the code will appear to do nothing on the first click.

 

[NateSC]

You could use a toggle button and the type of sort depends on the state of the button. When the file loads, you could force it to run one or the other sorts and also force the toggle to the appropriate position. You can even change the text and color on the toggle to make it clear which sort has been run.

Radio buttons are another simple alternative.