VBA - Setting Pivot Chart height at pivot table refresh

[ariez88]

Hi all,

I am using Sharepoint Excel services in my Excel workbook, 2010.

I have a pivot table whose data is populated from the database at certain filters. Associated with this table is a pivot chart that whose dimensions are fixed. The problem occurs when pivot table contains a large number of data rows because the chart remains at a fixed height and does not label all the rows in the graph. I have written following VBA code which gets the number of pivot table rows. Now I want to set the size of the pivot chart according to number of pivot table rows. Is there any function or property that I can all to set the height of Pivot chart? The ScaleHeight property used scales the current height of the chart. However, I want to set the height of the chart according to the number of pivot table rows.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AdjustChart
End Sub

Sub AdjustChart()
Dim countnonblank As Integer, myRange As Range
Set myRange = Columns("A:A")
countnonblank = Application.WorksheetFunction.CountA(myRange)
ActiveSheet.Shapes("Chart 5").ScaleHeight countnonblank, msoFalse, msoScaleFromTopLeft
End Sub
Any help will be much appreciated!

[p45cal]

A shape has a height property:
ActiveSheet.Shapes("Chart 5").Height = countnonblank*4
where you should adjust the 4 to your liking.

[ariez88]

Thanks for your reply.
It solved the problem to some extent, however, there is still a slight problem with this. Actually the pivot chart refreshes whenever pivot table updates according to some filters. And I want to re size the chart according to the number of pivot table rows. This code does so, but the chart does not label all the pivot table rows in it. I have given address of an image for your referral below, in which the chart only labels alternate pivot table rows. But again, this alternate-row-labelling is only for some filters. For other filters, the chart labels all the rows in it. How should I go about it ?

You can access the image at flicker.com at photos/84478018@N05/

[p45cal]

This is just a setting you have to adjust in the chart. If you right-click that axis (one of the category labels should do it) and choose Format Axis… then in the topmost Axis Options section, the second item down is Interval between labels, instead of Automatic, you should choose Specify interval unit and put a 1 in the field then close the dialogue box.

[ariez88]

Thankyou very much for your help!
I am all done!