# Changing chart series picture fill based on data point



## Monicasinha (Dec 26, 2022)

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 (Dec 27, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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


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

Does this help?


----------



## Monicasinha (Dec 29, 2022)

Yes, it worked! Thank you so so much


----------



## Domenic (Dec 29, 2022)

That's great, glad I could help.

Cheers!


----------

