VBA - Setting Pivot Chart height at pivot table refresh RRS feed

  • Question

  • 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!

    Thursday, September 20, 2012 6:27 AM