选择不同/不同值时,调整透视图的大小 [英] Resize pivot chart when selecting different less/more values
问题描述
使用VBA创建数据透视表时,我会根据图表中不同的值的数量设置图表的大小。使用透视图可以选择仅显示图表的某些值。所以例如如果我有这个图表:
然后我只选择2我得到这个:
这太大了,有时甚至会更大。我想要的是当用户选择较少以使其自动变小时自动调整大小。所以我希望它是这样的:
最后我找到了一个使用change事件的解决方案。所以当我选择不同的值时,我会检查我有多少值,然后根据值数量调整我的图表的大小。
这是一个我使用的例子(我有pre $ Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
如果Target.PivotTable.Name =PivotTable1,然后
表单(PerPublication)。形状(1).Width =(Target.PivotTable.RowRange.Count * 50)+ 100
结束如果
如果Target.PivotTable.Name =PivotTable2,然后
表单(PerPublication)。形状(2).Width =(Target.PivotTable.RowRange.Count * 40)+ 40
End If
GetOut:
如果Err.Description<> 然后
Err.Clear
如果
End Sub
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?
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
这篇关于选择不同/不同值时,调整透视图的大小的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!