Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to write a macro that would show/hide subtotals for dimensions, that are selected from list box. I found a macro to show/hide subtotals for the table, that has regular dimensions (where you can't select which dimensions to show):
'***********************************************************************************************************************************************
' Show Subtotals
'***********************************************************************************************************************************************
sub PivotShowSubtotals
set c = ActiveDocument.GetSheetObject("CH01")
set cp = c.GetProperties
set dims = cp.Dimensions
'set gp = chart.GetProperties
'set tp = gp.TableProperties
countexpr = cp.expressions.count 'count expressions
countdimexpanded = c.getcolumncount - countexpr 'count expanded dimensions
'msgbox(c.getcolumncount - countexpr)
for i = 0 to countdimexpanded -1
j = i+1
Answer = MsgBox("Show Subtotals: Column "& j,3) '3 = yes, no, cancel
If Answer=6 Then 'yes
dims(i).ShowPartialSums = true
c.SetProperties cp
'''' show total from dimension limit tab
c.SetProperties cp
elseif Answer=7 Then 'no
dims(i).ShowPartialSums = false
c.SetProperties cp
else
exit for '(Answer=2) 'cancel
End If
next
end sub
'***********************************************************************************************************************************************
' Hide Subtotals
'***********************************************************************************************************************************************
sub PivotHideSubtotals
set c = ActiveDocument.GetSheetObject("CH01")
set cp = c.GetProperties
set dims = cp.Dimensions
for i = 0 to dims.Count -1
dims(i).ShowPartialSums = false
next
c.SetProperties cp
end sub
But I need a macro, which would show/hide subtotals for particular dimensions, selected from listbox.
Please, help me
I have built such show/hide sub-totals with macros and wouldn't recommend to use such approach then it's not necessary - see: Macros are Bad - you could gain the same if you doubled each of your dimensions within the chart - one with sub-totals and one without - and use visibility-conditions on these dimensions maybe per if(substringcount(getfieldselections(DimensionsWithSubTotals), 'Dimension1') >= 1, 1, 0).
There are various examples available here in the community how you could show and hide dimensionss and expressions, maybe this one: Hiding Dimensions in Pivot Table.
- Marcus