Locating Items

We frequently add pictures and graphics to our charts.
This code shows you how to obtain the exact position of chart objects.
This relies on the XLM function GET.CHART.ITEM
This is not possible using the object model

GET.CHART.ITEM(x_y_index, point_index, item_text)


pass in 1 to return the x position
pass in 2 to return the y position


Is a number between 1 and 8 to specify exactly what you are looking for
1 - upper left
2 - upper middle - of a rectangular item such as a column chart
3 - upper right
4 - right middle
5 - lower right
6 - lower middle
7 - lower left
8 - left middle


A text description of the chart item you are interested in

The chart must be active for this to work

Public Sub RepositionArrow() 
Dim oActiveRange As Excel.Range
Dim dXVal as Double
Dim dYVal As Double
Dim oChart As Excel.Chart

   Set oRange = ActiveCell
'Activate the chart

   dXVal = ExecuteExcel4Macro("GET.CHART.ITEM(1,2,""S1P3"")")
   dYVal = ExecuteExcel4Macro("GET.CHART.ITEM(2,2,""S1P3"")")

   Set oChart = ActiveChart

'Convert XLM coordinates to Drawing Object coordinates
   dYVal = oChart.ChartArea.Height - dYVal

   oChart.Shapes("Arrow").Left = oChart.PlotArea.InsideLeft
   oChart.Shapes("Arrow").Top = oChart.PlotArea.InsideTop
   oChart.Shapes("Arrow").Width = dXVal - oChart.Shapes("Arrow").Left
   oChart.Shapes("Arrow").Height = dYVal - oChart.Shapes("Arrow").Top

End Sub

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext