Check pivot table sort order using VBA

[Vivek D]

I have a pivot table that shows Revenue, GM, Revenue vs Plan and GM vs Plan by Account.

I want users to have the ability to sort the table by clicking on each these columns.

To do this, I've decided to create 4 shapes (transparent) and place each of them above each of the data/value fields.

When they click on a field, it should call a macro and sort the pivot table based on that field.
There will be a default order, say descending and when they click on it again, it should do the reverse i.e. ascending.

Sorting can be done using code below but how do I check how the column is currently sorted? Based on that say if it is Ascending, sort by descending and vice-versa?

I'm open to any other suggestions to achieve the requirement too.

Sub PivotTableSort1()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

PvtTbl.PivotFields("Account").AutoSort Order:=xlAscending, Field:="Sum of Revenue"

End Sub

 

[Vivek D]

Is this that "one thing" that cannot be done using VBA too?

 

[Deepak]

Theses might be helpful.

http://www.mrexcel.com/forum/excel-...tions-check-if-column-alphabetical-order.html

http://excel.tips.net/T002395_Determining_Sorting_Criteria.html

http://www.mrexcel.com/forum/excel-questions/385494-column-sort-question.html

 

[p45cal]

Take a look at the attached. There's an activex textbox at cell C12 directly above a pivot table. The textbox's font is wingdings 3. There are several different arrows you can use to show the sort order (though it's also visible in the pivot table itself as well).
Doubleclick the textbox to see what happens, as well as what happens when you refresh the pivot.

The code, all in the sheet's code module:

Private Sub Label1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Set xxx = Label1 'this allows you to see the properties in the Locals pane of the vbe.
Select Case xxx.Caption
  Case Chr(199)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Head1").AutoSort xlDescending, "Head1"
    xxx.Caption = Chr(200)
  Case Else
ActiveSheet.PivotTables("PivotTable1").PivotFields("Head1").AutoSort xlAscending, "Head1"
    xxx.Caption = Chr(199)
End Select
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Me.Label1.Caption = Chr(50)
End Sub

 

[Vivek D]

Thanks. It helped. The key takeaway for me was that I should use a cell to track the sort order and then use that to decide whether to sort ascending or descending.

I ended up doing this. Not much of a VBA guy so I'm sure that code below can be written better but it served my purpose. Now I just need to make it such that it can work on any field within the pivot.

Sub Rectangle1_Click()

Dim SortOrder As String: SortOrder = Worksheets("Sheet1").Cells(1, "A").Value
 
  If SortOrder = "Ascending" Then
   
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Head1").AutoSort xlDescending, "Head1"
    Worksheets("Sheet1").Cells(1, "A").Value = "Descending"
 
  Else
   
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Head1").AutoSort xlAscending, "Head1"
    Worksheets("Sheet1").Cells(1, "A").Value = "Ascending"
 
  End If

End Sub

 

[NARAYANK991]

Hi Vivek ,

Another option is to use code such as the following :

Sub PivotTableSort1()
    Static xlOrder As XlSortOrder
    Dim PvtTbl As PivotTable

    If xlOrder = 0 Then
      xlOrder = xlAscending
    Else
      xlOrder = Abs(Not (xlOrder) Mod 2)
    End If
   
    Set PvtTbl = Me.PivotTables("PivotTable1")
    PvtTbl.PivotFields("Account").AutoSort Order:=xlOrder, Field:="Sum of Revenue"
End Sub

Each execution of this piece of code will toggle the sort order.

Narayan