I want to sort the table on clicking a button.
It should sort based on a single expression written in the chart always.
How to achieve that?
I used the below macro
Set ch = ActiveDocument.GetSheetObject("CH01")
ch.SortBy 8 '' this line specifies the expression to sort by
Set p = ch.GetProperties
Set dims = p.Dimensions
'' Dimensions start by 0
'' 0 = Disable sort
'' 1 = Sort ascending
'' -1 = Sort descending
i = dims.Item(0).SortCriteria.SortByNumeric
If i = 1 Then
dims.Item(0).SortCriteria.SortByNumeric = -1
dims.Item(0).SortCriteria.SortByNumeric = 1
It works fine, but here i have given the sortby "8" th expression, but when i click the next button, it will hide first 10 expressions and shows the next 10 expressions. so that sort order changes for the 2nd "8" expression enabled.
But i want the sort to remain for the 1st "8th" expression in the table.
If it is mendetory to have button to sort your table. then try this.
1. create a variable: say--- vsort
2. in the sort tab for expression sort put some condition like: if(vsort=1,expression)
here expression is your expression on which you want to sort the table
3. creat a button and set the variable value 1 on click.
It is better to avoid macros..
Problem with macro is: when it is activated it takes the current 8th expression from the chart...
when you are hiding the expressions then it gives wrong sorting...
the only trick which is coming to my mind is...
lets say you are having exp1 on which you need to sort your table...
take exp1 2 times in your table... now you will get two colomns with same expression that is exp1.
now make the Text color white for one of the column and header lable a space.. so that one column should not be visible..
now use that column no in your macro....
But I have around 120 expressions in a table, it keeps changing on clicking the next button,
so i cannot use that expression multiple times in the table.
I want to restrict the sort to use only the 1st "8th" expression as the sort order,
but in the above macro, it changes for the current "8th" expression.
And client requirement is, they should be able to sort every column manually, so i cannot restrict them from sorting manually. but i want the sort to retain even if they sort it manually on clicking a button.