Changing chart series picture fill based on data point

[Monicasinha]

Referrring to another post Changing chart series picture fill based on data point name

In my case, the file will be shared with multiple user across the globe. I therefore cannot reference picture from my local drive.
Can we reference the picture located within the same workbook? I have saved picture as name manager. But unsure how to access it through VBA.
I tried using below code however I am getting the error "Application defined or object defined error"

.UserPicture Range("RedTriangle") & ".PNG"
'where RedTriangle is Name of the picture.

I am stuck. Can you pls help here?

 

[Domenic]

You'll need to amend your code so that it first exports the image to the user's local temporary folder, and then assigns the path and filename to the UserPicture property. Then the saved image can be deleted from the temporary folder. Here's an example. First add the following function to your module...

Function ExportImage(ByVal saveAsFilename As String, ByVal shapeToExport As Shape, ByRef errorMessage As String) As Boolean

    On Error GoTo errorHandler
    
    shapeToExport.Copy
    
    Dim ws As Worksheet
    Set ws = shapeToExport.Parent
    
    With ws.ChartObjects.Add(Left:=0, Top:=0, Width:=shapeToExport.Width, Height:=shapeToExport.Height)
        .Activate
        With .Chart
            .ChartArea.Format.Line.Visible = msoFalse
            .Paste
            .Export Filename:=saveAsFilename
        End With
        .Delete
    End With
    
    ExportImage = True
    
    Exit Function
    
errorHandler:
    errorMessage = "Error " & Err.Number & ":" & vbCrLf & vbCrLf & Err.Description
    ExportImage = False
    
End Function

Then, assuming that the workbook running the code contains your image, you could do something like this . . .

Sub test()

    Dim saveAsFilename As String
    Dim errorMessage As String
    
    saveAsFilename = Environ("temp") & "\temp.png"
    
    If Not ExportImage(saveAsFilename, ThisWorkbook.Worksheets("Sheet1").Shapes("RedTriangle"), errorMessage) Then 'change the sheet name accordingly
        MsgBox errorMessage, vbCritical, "Error"
        Exit Sub
    End If
    
    'your code here
    '
    '
    
    .UserPicture saveAsFilename
    
    '
    '
    '
    
    Kill saveAsFilename
    
End Sub

Hope this helps!

 

[Monicasinha]

Thank you so much Domenic. However, I am unsure what mistake I am doing that I am getting the error 76 (Path not found).
Below is the full VBA code that I am using

Sub test()

Dim x As Integer
Dim varValues As Variant
Dim iCategory As Long
Dim vCategories As Variant
Dim Series As Long
Dim i As Long
Dim saveAsFilename As String
Dim errorMessage As String


If Not ExportImage(saveAsFilename, ThisWorkbook.Worksheets("Sheet1").Shapes("Diamond 1"), errorMessage) Then
MsgBox errorMessage, vbCritical, "Error"
Exit Sub
End If

With ActiveSheet
Set cht = ActiveSheet.ChartObjects("Chart1").chart
saveAsFilename = Environ("temp") & "\temp.png"

With cht.SeriesCollection(3)
varValues = .XValues
For x = LBound(varValues) To UBound(varValues)
If varValues(x) = "Price" Then
With .Points(x).Format.Fill
.Visible = msoTrue
.UserPicture saveAsFilename
.TextureTile = msoFalse
End With
End If
Next
End With
End With
Kill saveAsFilename
End Sub

 

[Monicasinha]

Just to add to my comments above: I can see file name in immediate window for Environ("temp") & "\temp.png".
However no file name appears when checking for saveAsFilename.
Unsure if issue is due to this or something else. Just wanted to mention about this in case this helps in solving.

 

[Domenic]

It looks like you've omitted this line at the beginning of the code . . .

saveAsFilename = Environ("temp") & "\temp.png"

Does this help?

 

[Monicasinha]

Yes, it worked! Thank you so so much

 

[Domenic]

That's great, glad I could help.

Cheers!