Autofit Pivot Graph in VBA

Resolved · Medium Priority · Version 2010

Mon 24 Mar 2014, 11:03 Delegate Sarah said...

Sarah has attended:
Excel VBA Intro Intermediate course

Autofit Pivot Graph in VBA

Hi, I am using data from different reports to create PDF reports for specific stakeholders based on the group they need to report on. I have combined all the data into one template workbook which can be updated on a monthly basis and recorded/written the code to filter/create PDFs for each stakeholder.

The only problem I have is that I cannot change the size of the pivot graph depending on the number of entries it is showing. Is there any way to add this to the code? Ideally the graph will expand (simple bar chart) to include all entries under a specific filter or get smaller where there are less options in the axis.

Thanks,

Sarah.

Mon 24 Mar 2014, 12:39 Trainer Jens said...

RE: Autofit Pivot Graph in VBA

Hi Sarah,

Thank you for the forum question.

What you want is not straight forward. You need to count number of entries (Pivot Items) and store the number in a variable. If number of entries is greater than maybe 300 then you want this graph size. In the example below I just recorded that I resized the graph and added the code to my decision code.

Sub ResizePivotGraph()
Dim iPTItems As Integer

iPTItems = ActiveSheet.PivotTables("MyPT"). _
PivotFields("Product").PivotItems.Count

If iPTItems <12 Then

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.Shapes("Chart 2"). _
ScaleWidth 0.6880585156, msoFalse, _
msoScaleFromTopLeft

ElseIf iPTItems <24 Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.Shapes("Chart 2"). _
ScaleWidth 2.06627018, msoFalse, _
msoScaleFromTopLeft
End If

End Sub

I hope that this will help you to do what you want.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu

Mon 24 Mar 2014, 16:35 Delegate Sarah said...

RE: Autofit Pivot Graph in VBA

Hi Jens,

Thanks so much for this response, it is really helpful!

I've come across two hiccups, firstly iPTItems is counting all items in the pivot table rather than just those showing when it is filtered, so instead of 35 it is reading a 860, is there anyway to count just the filtered items?

The second problem is that the Pivot Table and chart are on two different sheets so when I activate the chart the variable changes?

Thanks,

Sarah.

Mon 24 Mar 2014, 20:33 Trainer Jens said...

RE: Autofit Pivot Graph in VBA

Hi Sarah,

Sorry you are right it counts all items. This one is working for me

Sub ResizePivotGraph()
Dim iPTItems As Integer
Dim pf As PivotField
Dim pt As PivotTable
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables("MyPT")
Set pf = pt.PivotFields("Product") 'Product is the field name

Sheet("PivotTable").Select
With pt

For Each pi In pf.PivotItems
If pi.Visible = True Then
iPTItems = iPTItems + 1
End If
Next pi

End With
If iPTItems < 12 Then

Sheets("ChartSheetName").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1"). _
ScaleWidth 0.6880585156, msoFalse, _
msoScaleFromTopLeft

ElseIf iPTItems < 24 Then

Sheets("ChartSheetName").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1"). _
ScaleWidth 2.06627018, msoFalse, _
msoScaleFromTopLeft
End If

End Sub

I hope this will do the job for you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu

Tue 25 Mar 2014, 16:49 Delegate Sarah said...

RE: Autofit Pivot Graph in VBA

Thanks Jens!

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

##### displaying in Excel

When you get a series of hash symbols (####) appearing in some of your cells in a spreadsheet, this can make you think that you've make some kind of mistake.

This is a common misconception - what this actually means is that the cell is not wide enough to fully display the content of the cell.

All you need to do to see what is actually in the cell is to widen the column that the cell is in.

View all Excel hints and tips