CODEWITHSUNDEEP
vbaexceltextbox
hannsta
hannsta

Reputation: 109

Attaching a Textbox to a point or line on a chart in Excel/VBA

I was wondering how to attach a textbox to a point or line in an Excel chart for the macro I am working on. I have been using the .AddTextbox method such as 

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

But I have to then manually drag the textbox over the line on the chart it is representing as the orientation is of the chart not the line. Is there a way to convert the line/point to a chart orientation which I could use as a variable? or another way? Possibly using the datalabel function, though I want to be able to customize one of the axis locations. Thanks

Upvotes: 5

Views: 13047

Answers (2)

suiluj_julius
suiluj_julius

Reputation: 110

Another option would be to use the data labels of Excel. I see two more elegant options:

  1. Make a new data series with just one entry in your chart, give the series the coordinates and the name of the label you want to see. Now activate the marker option for the series (if not done already), right-click on the data point, click "add data labels". Now you'll see the y-Value of the point. By right-clicking again and choosing "Format Data Labels" you can change the text to the series name, also the position, the border, etc. are modifiable. Below an example with two data points. You could delete the second point, the line and the marker but like this you see how it works. Data label example
  2. Similarly to the solution from KazJaw you can use the actual data points of your series for attaching custom data labels. This requires some coding, I used this for the chart named "Topview" and wrote percentages 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

Upvotes: 3

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

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

  1. chart itself, which position is set in relation to top-left corner of sheet range area
  2. point in series which position is set in relation to top-left corner of chart

Combination of both result with the following code (fixed parameters-required changes to your situation, could be more dynamic with loop)

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 result presenting the picture below.

enter image description here

Upvotes: 4

Related Questions