98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Autofit Pivot Graph in VBA
Autofit Pivot Graph in VBA
Resolved · Medium Priority · Version 2010
Mon 24 Mar 2014, 11:03Delegate 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:39Trainer 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:35Delegate 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:33Trainer 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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:##### displaying in ExcelWhen 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. |
