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)


x_y_index

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


point_index

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


item_text

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
   ThisWorksheet.ChartObjects(1).Activate

   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

   oRange.Activate
End Sub


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