Is it possible to insert text boxes/labels into a chart using vba?

Question

I'm currently writing a program that will graph an x-ray spectrum, and then calculate where all of the peaks are, and what they should be labeled. However, I'm not sure how I can add the labels of each peak using excel vba. I know the x and y coordinate of the tip of the peak on the chart (based on the axes units). Is there any way to use that information to place a label slightly above, or slightly to the right of that point?

Below, I've included an example of what I'm going for.

The peaks will be different elements based on what data is input. The tips of the peaks will also be in different x and y positions. So I think it would be best if there was some way to add the label based on x and y coordinates of the tips of the peaks, since I always know those values.

Looking around on the internet, I can't find any real information on how to do this, if it's even possible. I'm hoping you guys can help me out!

Answer 1

Definitely do-able. You can add shapes (like textboxes) to the ChartArea object. However, in this case it seems like it would be easier to just hijack the points' DataLabel object and manipulate the text therein.

Dim pt As Point
Dim p As Long
Dim dl As DataLabel
Dim srs As Series
Dim cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
Set srs = cht.SeriesCollection(1)
srs.HasDataLabels = False '## Turn off the data labels
For Each pt In srs.Points
    p = p + 1
    If [logic or function to determine your peaks] Then  
        pt.HasDataLabel = True
        Set dl = pt.DataLabel
        dl.Text = "whatever you want it to be"
    End If
Next