VBA - Scatterplot labels - images instead of "text"

[mrgrumpball]

Hi,

I have been regularly using the scatterplot in excel, with the well working macro to add labels to (https://support.microsoft.com/en-us...-labels-to-data-points-in-an-xy-scatter-chart) markers. Text is just sitting in the column left to the coordinates. This works perfectly for text.

I am now looking for an adaptation of the macro, where

• "images" would be inserted in the lable instead of the text,
• source of the images: a list of images in a specific location (local drive, or url). Generrally, there are about 70 to 100 images to insert.
• Format would be 80x50 pixels.
• Ideally the size of the label box would also be adapted in the so the image does not get distorted

An adaptation that was suggested but does not seems to work was based on userpicture (Scatter Plot Labeling with Images). This returns an error. Can anyone suggest the changes to makes

Let's say that the path and filename for your images are located in the corresponding cells one column to the right of the series values, replace...




For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

with

For Counter = 1 To Range(xVals).Cells.Count
With ActiveChart.SeriesCollection(1).Points(Counter)
.HasDataLabel = True
With .DataLabel.Format.Fill
.UserPicture Range(xVals).Cells(Counter, 1).Offset(0, 1).Value
.Visible = msoTrue
End With
End With
Next Counter

 

[John_w]

Welcome to MrExcel forums.

What's the error and which line causes it? Always post these details because it helps people to help you.

Following the MS example, put the full path and file name of each image in column D (D2:D6), then run this macro with the Chart1 sheet active:

Public Sub AttachImagesToPoints()

    Dim i As Long, xVals As String
    
    'Disable screen updating while the subroutine is run.
    Application.ScreenUpdating = False
    
    'Store the formula for the first series in "xVals".
    xVals = ActiveChart.SeriesCollection(1).Formula
    
    'Extract the range for the data from xVals.
    xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
    xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
    Do While Left(xVals, 1) = ","
        xVals = Mid(xVals, 2)
    Loop
    
    'Attach an image to each data point in the chart.
    For i = 1 To Range(xVals).Cells.Count
        With ActiveChart.SeriesCollection(1).Points(i)
            .HasDataLabel = True
            .DataLabel.Text = ""
            With .DataLabel.Format.Fill
                .UserPicture Range(xVals).Cells(i, 1).Offset(0, 2).Value
                .Visible = msoTrue
            End With
        End With
    Next
 
    Application.ScreenUpdating = True
    
End Sub