How to use Button to show a data as an ascending order??

[#1]

I make a pivot table, through which I show a Date & SaleAmount. I want to creat a button, when I click on this button the table show me the data as an ascending order or vice versa. Please help me...

Regards,

Anup

[kaushiknsolanki]

HI,

    What dimension you want to sort and by what order. Specify the order of the dimension.

Regards,

Kaushik Solanki

[#3]

Hi,

Koushik as a dimenssion I use Date & I sort it as a decending order. But I want make a button through which I want to change the order of my date as an ascending order. That means I want to give the two option to the user who want to view this table!!!!!! Thank You for your responce.

Regards,

Anup

[kaushiknsolanki]

Hi,

    I assume that the first dimension is Date.

    Here is the macro

Sub SortAsc
chart=ActiveDocument.GetSheetObject("CH01")
set Prop = chart.GetProperties
set vars = Prop.Dimensions
vars.Item(0).SortCriteria.SortByNumeric = 1 'For Asc.
chart.SetProperties Prop

End sub

Sub SortDesc
chart=ActiveDocument.GetSheetObject("CH01")
set Prop = chart.GetProperties
set vars = Prop.Dimensions
vars.Item(0).SortCriteria.SortByNumeric = -1 'For Desc.
chart.SetProperties Prop

End sub

Now create two button one for asc and second for desc.

In first button give action as external->macro and give name as SortAsc.

Same way for Desc.

Regards,

Kaushik Solanki

[Jason_Michaelides]

Hi Anup,

It's easier to change the field to sort by than to flip the sort order but try creating 2 variables:

VSortOrder with a value of 1

vSort with a value of =DateField

Then, create a button with 2 sequential actions, both External > Set Variable:

1. vSort - IF(vSortOrder=1,'=DateField*-1','=DateField')

2. vSortOrder - IF(vSortOrder=1,-1,1)

In the sort tab of your pivot table, check the option to use an expression and put vSort in there (might need an = sign)

I've not been able to test this so hope it works for you!

Jason

[#6]

Thank You Koushik. Is it possible with out macro???

[#7 · Accepted solution]

Hi,

Below attached a working example.

HTH

Reg,

Shubhu

[#8]

Thank You to all of you..........