Can a user hide an expression in a chart?

[jedgson]

Hi,

I have a chart which has 4 expressions:

Total (Displayed as a Bar)

% X of total (Displayed as a Line)

% Y of total (Displayed as a Line)

total X of total (i.e. if Total = 200 and '% X of total' = 50% then 'total X of total' would = 100) (Displayed as a Line)

What I would like to be able to do is allow the end user to select which expressions are visible on the chart. Is this possible?

Jay

[Anonymous · Accepted solution]

Jay,
It is possible using macro. You'll need a button for each expression which you want to hide/show. Macros will be like these:

sub Test
 set chart = ActiveDocument.GetSheetObject("CH01")
 cp = chart.GetProperties
 set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData
 if expr.Enable = 0 then
 expr.Enable = true
 else
 expr.Enable = false
 end if
 chart.SetProperties cp
end sub

[johnw]

Attached is the approach we're using. It's probably overkill for your situation, but you may be able to mine information from it. I haven't checked if it works in 9.0, but it works in 8.5.

[jedgson]

John,

This is perfect for what I need thanks.

I am tryin to ammend the Macro to set properties for the Dimensions in the Chart.

I am trying to set the 'Suppress Null values' and change the sort to 'Load Order'

For suppress Null Values I have added this:

chartDimensionMax = chart.GetColumnCount - 1
set dims = chartProperties.Dimensions
for i = chartDimensionMax to 0 step -1 'remove existing dimensions
 chart.removeDimension(i)
next
for j = 0 to selectedDimensionMax 'add selected dimensions
 chart.addDimension(selectedDimensions.Item(j).Text)
 dims(j).NullSuppression = true
next

Unfortunately this keeps failing with 'Invalid procedure call or argument'

Can you see where I am going wrong.

Jay

[jedgson]

I have managed to do this now. Thanks for your help

Jay