VBA: Find coordinates of markers on chart and attach a textbox to them

[vlad7984]

Hello, I have a 2 data series. The 2nd data series only consists of points that I want shown on the first data series. So I have a price and date series with markers showing up only on specific dates. I would like to attach text boxes, or place text boxes, near those markers. I figured one way to do that would be to find the coordinates of the markers on the chart and then use those coordinates to add a text box.

Is this possible with VBA?

 

[Domenic]

Have you considered using data labels? In any case, the following code will add textboxes to data points that contain markers for the specified series and for the specified chart. The textboxes will be placed just above the data point. You can adjust the position for these textboxes as desired. You can also adjust the height and width.

Option Explicit

Sub test()


    Dim oChart As Chart
    Dim seriesIndex As Long
    
    'Set the chart (change the sheet and chart references accordingly)
    Set oChart = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    
    'Specify the series number (change accordingly)
    seriesIndex = 1


    AttachTextBoxesToMarkers oChart, seriesIndex
    
End Sub


Sub AttachTextBoxesToMarkers(oChart As Chart, seriesIndex As Long)


    If seriesIndex <= 0 Or seriesIndex > oChart.SeriesCollection.Count Then
        MsgBox "Series index is out of bounds!", vbExclamation
        Exit Sub
    End If
    
    Dim shp As Shape
    Dim pt As Point
    Dim pointIndex As Long
    
    Const TEXTBOX_HEIGHT As Long = 15
    Const TEXTBOX_WIDTH As Long = 40
    Const GAP As Long = 5
    
    With oChart.SeriesCollection(seriesIndex)
        For pointIndex = 1 To .Points.Count
            If .Points(pointIndex).MarkerStyle <> -4142 Then
                Set pt = .Points(pointIndex)
                With pt
                    Set shp = oChart.Shapes.AddTextbox(msoTextOrientationHorizontal, .Left, .Top - (TEXTBOX_HEIGHT + GAP), TEXTBOX_WIDTH, TEXTBOX_HEIGHT)
                End With
                With shp
                    With .Line
                        .Visible = msoTrue
                        .ForeColor.RGB = RGB(0, 0, 255) 'change colour accordingly
                    End With
                    .TextFrame2.TextRange.Text = "YourTextHere"
                End With
            End If
        Next pointIndex
    End With
    
End Sub

Hope this helps!

 

[Jon Peltier]

As Domenic says, look into DataLabels.

 

[vlad7984]

Hi, yes I've looked into data labels and it seems to work well. The problem is that I am linking text from a cell and there is a 255 character limit so my text gets cut off. Is there any way to circumvent this?

 

[Jon Peltier]

Yes, now I recognize your name in your handle.

The limit seems to be 255 characters for a data label, or 256 characters if you use the Value from Cells option. These limits seem impossible to get around. I even tried giving a series a long name and using the series name in the data label, and still came up against the limit of 255 characters.

 

[vlad7984]

Thanks for the response and sorry about the multiple posts!