What's different beetwin Left property in Char

Question

I used the code below to set TableChart's position with it's related PivotTable position. My sheets direction is Right to Left.

Sheets("sheet1").ChartObjects("InsuranceChart").Top = Sheets("sheet1").PivotTables("pvtReport").TableRange1.End(xlDown).Offset(2).Top

Sheets("sheet1").ChartObjects("InsuranceChart").Left = Sheets("sheet1").PivotTables("pvtReport").TableRange1.Left

The problem is in second line, returned value of two Left property. (I checked this two with Msgbox) Left one that is for ChartObjects, returned 884565.76 and right one that is for TableRange1 returns 843.75! And the PivotChart been hide after running second line.

How can I made right side of PivotChart equal than right side of PivotTable? My PivotTable position is not constant and changes in corporate situation by macro.

Have anyone know that is the whole sheets width?

Answer 1

On my test_workbook Your macro works fine. Macro positions top-left corner of Chart in "A12" Cell and pivot table takes range "A3:B10", so everything is ok.

Answering the second question. First of all You have to use worksheet events (put updating code directly to sheet1) to update position of chart, when You change values/fields in pivot table. For example:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) update_position... End sub

Second, in vba You must use top and left properties to position objects. However You can use object width and height properties to correct position of Your chart. As I said before Your macro works fine, may be something is wrong with names (As You said pivot table position is not constant may be name also.) of objects and it correspond to other object in sheet so it works, but not correct. Assuming that table (left and top) position is given by Your macro, You only need to add to Your code something like that:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
     Sheets("sheet1").ChartObjects("InsuranceChart").Top = Sheets("sheet1").PivotTables("pvtReport").TableRange1.End(xlDown).Offset(2).Top
     Sheets("sheet1").ChartObjects("InsuranceChart").Left = Sheets("sheet1").PivotTables("pvtReport").TableRange1.Left
     Sheets("sheet1").ChartObjects("InsuranceChart").Width = Sheets("sheet1").PivotTables("pvtReport").TableRange1.Left + Sheets("sheet1").PivotTables("pvtReport").TableRange1.Width
End Sub

And if You don't want to change chart width it might be a problem because when Your table will be in A Column, the chart might go out of a screen. Anyway fast solution is here:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If Sheets("sheet1").PivotTables("pvtReport").TableRange1.Left = 0 And Sheets("sheet1").ChartObjects("InsuranceChart").Left = 0 Then GoTo Ender
    Sheets("sheet1").ChartObjects("InsuranceChart").Left = Sheets("sheet1").PivotTables("pvtReport").TableRange1.Left + Sheets("sheet1").PivotTables("pvtReport").TableRange1.Width - Sheets("sheet1").ChartObjects("InsuranceChart").Width
    Sheets("sheet1").ChartObjects("InsuranceChart").Top = Sheets("sheet1").PivotTables("pvtReport").TableRange1.End(xlDown).Offset(2).Top

Ender:
End Sub

It checks where is right edge of table and subtracts width of chart. I've added also fail-safe if chart and table are on A column macro terminates to avoid minus left chart position.

Answer 2

If i understood your problem properly, the location is relative on a right to left workbook Range("xer11:xee29") will be located on Range("z11:m29") on a right to left workbook.
tested it and it works.

Private Sub CommandButton1_Click()

Dim rngchart As Range
Set rngchart = Range("xer11:xee29")
Dim chrt As ChartObject
Set chrt = Sheets("sheet1").ChartObjects("InsuranceChart")
chrt.Top = rngchart.Top
chrt.Left = rngchart.Left
chrt.Width = rngchart.Width
chrt.Height = rngchart.Height

End Sub

Answer 3

Seems there were great different between TableRange1.Left and ChartObject().Left calculation.

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

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).

Sheets("Sheet1").ChartObjects("InsuranceChart").Top = _
Sheets("Sheet1").PivotTables("pvtReport").TableRange1.End(xlDown).Offset(2).Top
Sheets("Sheet1").ChartObjects("InsuranceChart").Left = _
Worksheets("Sheet1").Cells.Width - _
(Sheets("Sheet1").ChartObjects("InsuranceChart").Width + _
Sheets("Sheet1").PivotTables("pvtReport").TableRange1.Left)

We can calculate Left side of PivotTable from Right side of it; and its Width and WorkSheets width, then assign this value to left corner of PivotChart by ChartObject().Left property of PivotChart.