VBA 图例和绘图区域调整大小

VBA legend and plot area resizing

提问by DutchArjo

I have an Excel chart which changes on selections made in slicers. I noticed that the plot area and the legend area change depending on the made selection.

我有一个 Excel 图表，它会更改切片器中所做的选择。我注意到绘图区域和图例区域根据所做的选择而变化。

I tried to fix the position and size for the plot area using vba, but this simply does not work unfortunately.

我尝试使用 vba 修复绘图区域的位置和大小，但不幸的是这根本不起作用。

The plot area and the legend keep on resizing, causing the legend overlapping the plot area. Which I obviously do not want.

绘图区域和图例不断调整大小，导致图例与绘图区域重叠。我显然不想要。

I have this code, placed in the worksheet page of the vba editor:

我有这个代码，放在 vba 编辑器的工作表页面中：

Option Explicit
Private Sub Chart_Calculate()

ChartObjects("grafiek 4").Activate
ActiveChart.PlotArea.Width = 637.783
ActiveChart.Legend.Left = 716.514
ActiveChart.Legend.Width = 176.735
ActiveChart.Legend.Height = 295.334

End Sub

having this code, I assumed the automatic resizing would be gone, but I saw the legend sometimes still overlaps te plot area.

有了这个代码，我假设自动调整大小会消失，但我看到图例有时仍然与绘图区域重叠。

Is there a solution which permanently fixes this problem?

是否有永久解决此问题的解决方案？

Edit1:

编辑1：

Yesterday, I simply added a few parameters for the plot area. It seemed to work then. But now I tried again, and the legend is overlapping the plot area again.

昨天，我只是为绘图区域添加了一些参数。那时似乎奏效了。但是现在我又试了一次，图例再次重叠了情节区域。

I changed the code to:

我将代码更改为：

Option Explicit
Private Sub Chart_Calculate()

ChartObjects("grafiek 4").Activate
ActiveChart.PlotArea.Top = 33.102
ActiveChart.PlotArea.Left = 67.1
ActiveChart.PlotArea.Width = 637.783
ActiveChart.Legend.Top = 7
ActiveChart.Legend.Left = 716.514
ActiveChart.Legend.Width = 176.735
ActiveChart.Legend.Height = 329.667

End Sub

So with 2 more paramters for the plot area.

因此，绘图区域还有 2 个参数。

edit2:I have checked the legend properties in Excel. under 'options for legend' there is a checkbox: show legend without overlapping plot area (I do not know the exact english text). This box is checked, but it does overlap the plot area.

edit2：我检查了 Excel 中的图例属性。在“图例选项”下有一个复选框：显示没有重叠绘图区域的图例（我不知道确切的英文文本）。此框已选中，但它确实与绘图区域重叠。

Why is it impossible to achieve this? Having fixed sizes for the plot area and the legend should not be so hard.

为什么不可能实现这一目标？绘图区域和图例的固定大小不应该那么难。

edit 3:I do have this routine now in my workbook:

编辑 3：我现在在我的工作簿中有这个例程：

Option Explicit
Private Sub Chart_Calculate()

ChartObjects("grafiek 4").Activate
With ActiveChart
    With .PlotArea
        .Top = 33.102
        .Left = 67.1
        .Width = 637.783
    End With
    With .Legend
        .IncludeInLayout = True
        .Position = xlLegendPositionRight
        .AutoScaleFont = False
        .Font.Size = 8
        .Top = 5
        .Left = 706.899
        .Width = 179.735
        .Height = 336.681
    End With
End With

End Sub
Sub kopieergrafiek()

ActiveSheet.ChartObjects("Grafiek 4").Copy

End Sub

(including the suggestion in the comment below my post)

（包括我帖子下方评论中的建议）

I does not seem to work. Does worksheet_change event perhaps works better?

我似乎没有工作。worksheet_change 事件可能效果更好吗？

edit 4:I still do not have a solution for this issue. It already happens when the name of 1 of the legend items is to long to fit the space. And it also happens when there are to many items in the legend to fit in the space available.

编辑 4：我仍然没有解决此问题的方法。当 1 个图例项的名称太长以适应空间时，它已经发生了。当图例中有很多项目适合可用空间时，也会发生这种情况。

I think there is no solution for this. Unless I could somehow tell excel to maximize the number of items in the legend. or to maximize the length of the series name.

我认为没有解决方案。除非我能以某种方式告诉 excel 最大化图例中的项目数。或最大化系列名称的长度。

回答by hedgepig

I was having this problem myself with the legend resizing the plot area. I tried what Portland Runner suggested, only setting .Legend.IncludeInLayout to false (thus separating the legend from the plot area as he suggested, perhaps he made a typo?) and my plot area was no longer resized.

我自己在调整绘图区域大小的图例时遇到了这个问题。我尝试了 Portland Runner 的建议，只将 .Legend.IncludeInLayout 设置为 false（因此按照他的建议将图例与绘图区域分开，也许他打错了？）并且我的绘图区域不再调整大小。

回答by 2PL

I also had this problem and found this answer. I found a fix that works for me. Not sure why exactly it works but I do these steps:

我也遇到了这个问题并找到了这个答案。我找到了一个对我有用的修复程序。不知道为什么它会起作用，但我执行以下步骤：

• Set the legend position
• Undock the legend (legend.includeLayout = false)
• Resize the plot area to the size I wanted
• Re-dock the Legend (legend.includeLayout = True)
• Set the Legend.Left position and after that the legend is correctly positioned and lined up.

• 设置图例位置
• 取消停靠图例（legend.includeLayout = false）
• 将绘图区域调整为我想要的大小
• 重新停靠图例（legend.includeLayout = True）
• 设置 Legend.Left 位置，然后正确定位和排列图例。