_更改移动数据透视图的事件过程 [英] _Change event procedure for moving Pivot Chart

问题描述

_Change sheet的事件是什么？
我在_Change事件过程中遇到问题，而此代码真正在其他子程序中工作。

What is true with _Change sheet's event? I have bellow problem in _Change event procedure while this code works in other subroutines truly.

此宏用于移动数据透视图。

This macro wrote for moving Pivot Chart.

Microsoft Excel将关闭并遇到此错误：
Microsoft Excel遇到问题，需要关闭。对此造成的不便，我们表示歉意。并将关闭。

Microsoft Excel will close and encounter this error: Microsoft Excel has encountered a problem and needs to close. We are sorry for the inconvenience. and will close.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("sheet1").PivotTables("pvtReport").TableRange2.Cut
Cells(Sheets("sheet1").ListObjects("tblReport").Range.Rows.Count + 2, 13).Select
Sheets("sheet1").Paste
ActiveSheet.PivotTables("PvtReport").PivotSelect "", xlDataAndLabel, True
Selection.End(xlDown).Select
Cells(Selection.Row + 2, Selection.Column).Select

Sheets("sheet1").ChartObjects("InsuranceChart").Cut
Sheets("sheet1").Paste
End Sub

请注意，此代码在 Sheets（sheet1 ）.ChartObjects（InsuranceChart）。Cut line。

Please note that this code encounter error in Sheets("sheet1").ChartObjects("InsuranceChart").Cut line.

推荐答案

感谢朋友的指导，我特别从右到左找到适合的答案：

With thank of friends guidance, I reached to suitable answer specially for Right to Left sheet:

似乎在 TableRange1.Left 和 ChartObject（）。左计算。

其实 TableRange1.Left 与表格相关方向和 ChartObject（）。左不相关，从表的左侧计算。

In fact TableRange1.Left related to sheets Direction, and ChartObject().Left is not related, and calculate from left side of sheet.

在下面的代码中解决了这个问题，后面加上了解释。

The problem solved in code below with addition explanation after.

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveSheet.Name = "Sheet1" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim ChartLeft, SheetWidth As Double

    SheetWidth = Worksheets("sheet1").Cells.Width

    ChartLeft = SheetWidth - _
    (Sheets("Sheet1").ChartObjects("InsuranceChart").Width + _
    Sheets("Sheet1").PivotTables("pvtReport").TableRange1.Left)

    Sheets("Sheet1").ChartObjects("InsuranceChart").Top = _
    Sheets("Sheet1").PivotTables("pvtReport").TableRange1.End(xlDown).Offset(2).Top
    Sheets("Sheet1").ChartObjects("InsuranceChart").Left = ChartLeft

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If

End Sub

说明：

我们使用工作表的宽度来计算左边的距离数据透视图从工作表的左上角，从右到左表单;从数据透视表的右角到WorkSheet的右侧的距离。

Explanation:

We used sheet's width to calculate distance between left side of PivotChart from Left corner of WorkSheet, in the Right to Left sheet; from distance of Right corner of Pivot Table to Right side of WorkSheet.

请注意， TableRange1.Left 是距离工作表右侧的数据透视表右侧（从右至左工作表方向）和 ChartObject（）。左是数据透视图的左角距工作表左侧的距离（在两个WorkSheets方向上）。

Note that TableRange1.Left is distance of the Right corner of PivotTable from Right side of WorkSheet, (In Right to Left Worksheets directional) and ChartObject().Left is distance of Left corner of PivotChart from Left side of Worksheet (In both WorkSheets direction).

由于PivotChart位置和数据透视表之间的关系，我们希望PivotChart位置被用于相应的数据透视表，因此计算数据透视表的左侧右边的宽度和WorkSheets宽度，然后通过 ChartObject（）将此值分配给PivotChart的左上角左侧属性PivotChart。

And because of relation between PivotChart position and PivotTable, that we want PivotChart position is functioned to respective PivotTable, thus calculate Left side of PivotTable from Right side of that, and its Width and WorkSheets width, then assign this value to left corner of PivotChart by ChartObject().Left property of PivotChart.

这篇关于_更改移动数据透视图的事件过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！