Attach a text box to a point or line in a chart in Excel / VBA

Question

I was wondering how to attach a text box to a point or row in an Excel spreadsheet for the macro I'm working on. I used the .AddTextbox method, for example

 .Shapes.AddTextbox(msoTextOrientationHorizontal, 150, 250, 100, 15) _ .TextFrame.Characters.Text = "Temperature" 

But I have to manually drag the text box along the line in the diagram that it represents, since the orientation is a graph, not a line. Is there a way to convert the line / point to the orientation of the chart, which I could use as a variable? or in another way? It is possible to use the datalabel function, although I want to be able to configure one of the axis locations. Thanks

Answer 1

To solve your question, you need to get the left and top position of two objects:

• the position of which is set relative to the upper left corner of the sheet range region.
• a point in a row whose position is set relative to the upper left corner of the graph

The combination of both results with the following code (fixed parameters - the required changes in your situation, may be more dynamic with the cycle)

 Sub Add_Text_to_point() Dim tmpCHR As ChartObject Set tmpCHR = Sheet1.ChartObjects(1) 'put index of your chartobject here 'for first serie, for point 2nd here '(change accordingly to what you need) With tmpCHR.Chart.SeriesCollection(1).Points(2) Sheet1.Shapes.AddTextbox(msoTextOrientationHorizontal, _ .Left + tmpCHR.Left, _ .Top + tmpCHR.Top, _ 100, 15) _ .TextFrame.Characters.Text = "Temperature" End With End Sub 

After that, the image will be shown below.

Answer 2

Another option is to use Excel data labels. I see two more elegant options:

• Create a new series of data with one record in the chart, give a series of coordinates and the name of the label that you want to see. Now activate the marker parameter for the series (if it has not already been done), right-click on the data point, click "add data labels". Now you will see the y-value of the point. By right-clicking again and selecting "Format Data Labels", you can change the text to the name of the series, as well as change the position, border, etc. Below is an example with two data points. You can delete the second point, line and marker, but as you can see how it works.
• Similar to KazJaw's solution, you can use the actual data points of your series to attach custom data labels. This requires some coding, I used this for a chart called "Topview" and wrote the percentage values ​​next to the data point

 Sub Add_Text_to_data_points() percentages(1) = 0.1 percentages(2) = 0.23 '.... further entries chartNumber = findChartNumber("Topview") collNumber = 12 ' index of the "points" series Set tmpCHR = ActiveSheet.ChartObjects(chartNumber) For i = 1 To tmpCHR.Chart.SeriesCollection(collNumber).Points.count With tmpCHR.Chart.SeriesCollection(collNumber).Points(i) If percentages(i) <> 0 Then .DataLabel.Text = format(percentages(i), "0%") End If End With Next End Sub