Resize pivot chart when selecting different less/m

Question

When creating the pivot chart using VBA, I set the size of the chart depending on the number of different values that I have in the chart. With pivot charts you have the option to select only some values of the chart. So for example if I have this chart:

And then I select only 2 I get this:

This is too big and sometimes it can be even bigger. What I would like is to resize it automatically when a user select less so that it automatically become smaller. So I would like it to be something like this:

Is there any way to change the width automatically using VBA?

Answer 1

Finally I found a solution using the change event. So when I select different values I check how many values I have and then resize my chart depending on the values quantity.

Here is an example of what I used (I have 2 different pivot tables in my sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo GetOut
    If Target.PivotTable.Name = "PivotTable1" Then
        Sheets("PerPublication").Shapes(1).Width = (Target.PivotTable.RowRange.Count * 50) + 100
    End If
    If Target.PivotTable.Name = "PivotTable2" Then
        Sheets("PerPublication").Shapes(2).Width = (Target.PivotTable.RowRange.Count * 40) + 40
    End If
GetOut:
    If Err.Description <> "" Then
        Err.Clear
    End If

End Sub

Answer 2

You may want to take a look at these: resize (mrexcel) & Count number of series (stackoverflow)

In these sources there are some code snippets for getting the number of series in a chart and resizing charts to a fit to a range in the worksheet

Im by no means an expert coder, but with the code and concepts in the above you may be able to do something like this to a given ChartObject:

    'get the number of series in a given ChartObject:
    numberOfSeries = ChartObject.Chart.SeriesCollection.Count

    'you can use this to specify a width in cells/as a range. For the purpose of this example I 
    'eventually want the chart to cover 1 cell width for each series, + 1.
    myChartWidth = numberOfSeries + 1

    'Give the chart a name. Smarter people may supply a solution that reference the ChartObject 
    'directly, but I am not that clever
    ChartObject.Name = "Your Chart"

    'I will use A1 as reference for setting width, change as you see fit
    Sheet1.Shapes("Your Chart").Width = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, myChartWidth)).Width    

    'I assume you want to fix the height, adjust to your requirements
    Sheet1.Shapes("Your Chart").Height = Sheet1.Range("A1:A10").Height

    'If you want to also place the chart top left corner in the in the top left of A1, you can do the following
    Sheet1.Shapes("Your Chart").Left = Sheet1.Cells(1, 1).Left
    Sheet1.Shapes("Your Chart").Top = Sheet1.Cells(1, 1).Top

You probably want to place and size it differently, but I hope the ideas or the linked sources can get you closer to a solution