Using VBA is it possible to load an image into a shape

[geopetlas]

I was just wondering if it is possible to use VBA that could load an image, stored locally, into a shape on the worksheet?

Purpose for this is that I have a graph and I would like to position an unfilled shape behind the plot area, which has zero transparency, and that shape then would have a different image loaded in it depending on the value entered in a specific cell.

Already have some code that would automatically run the macro. Just need the picture into a shape code.

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$B$3" Then
Application.EnableEvents = False


' code for picture into shape should go here


Application.EnableEvents = True


End If


End Sub

Am using Excel 2016

Thanks in advance
George

 

[RickXL]

Hi,

Two things: The macro recorder works quite well and did you know that you can add a background directly to a chart? There is no need to add another shape - unless you want to, of course.

I added a picture to the background of my "Chart 6" by using this code;

Sub Macro1()
    With ActiveSheet.Shapes("Chart 6").Fill
        .Visible = msoTrue
        .UserPicture "C:\Users\RickXL\Pictures\HW Diagrams\Gigaport.png"
        .TextureTile = msoFalse
    End With
End Sub

To add a picture to a shape, just change the name:

Sub Macro2()
    With ActiveSheet.Shapes("Oval 7").Fill
        .Visible = msoTrue
        .UserPicture "C:\Users\RickXL\Pictures\HW Diagrams\Gigaport.png"
        .TextureTile = msoFalse
    End With
End Sub

Just change the path name to the one you want to use.

 

[geopetlas]

Morning Rick,

Thanks for the code. It works great. I changed it a bit to recognize a cell which contained the value from a list.
Whenever an item is selected from the list the background of the plot area will show a picture of the item that the graph represents.

George

 

[RickXL]

Great idea. I was pleased I could help.

Regards,

 

[geopetlas]

Well I thought I had it working.

When making a selection from a validated list (Sheet1 B3) nothing happens. No image loaded in a shape.
I made a button and assigned Macro2 to it and then when making a selection from the list I clicked the button and THEN it worked. An image was indeed loaded into Rectangle 2.

But I need to have it so when a selection is made from B3, excel recognizes that the value in B3 has changed and then runs Macro2 using said value of B3 without having to click a button.
So in other words, just one step; make a selection.

Below is the code for Macro2 placed in module 1

Sub Macro2()
    
    Dim strName As String
    strName = Sheet2.Range("C5").Value
    Sheet1.Range("G4").Value = Sheet2.Range("B5")
    
    With Sheet1.Shapes("Rectangle 2").Fill
        .Visible = msoTrue
        .UserPicture strName 
        .TextureTile = msoFalse
    End With
End Sub

Here is the code that I hoped would recognized the change in B3 but I can't seem to get it to work. I've placed it in the module and then in Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" Then
        'Insert some code here
'''' Trying to see if anything happens
        Sheet1.Range("A10").Value = Sheet2.Range("B5")
    End If
End Sub

I tried putting the code from Macro2 in where it says, "Insert some code here," even tried assigning values to cells just to see if the code works. NOPE!

Sure could use some help with this!
Sure hope I explained the problem and my need well enough.
George
Excel 2016

 

[RickXL]

Sorry, but I am not entirely sure what you are doing ...

However, this is what I did:

• Removed some extraneous lines from the Fill code.
• Inserted a list of full path names into a worksheet to use as a data validation list.
• Applied data validation to cell C5 of the worksheet that contained my shape.
• Added a worksheet change macro to the sheet. Code here:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$5" Then
        With Shapes("Oval 1").Fill
            .UserPicture Target.Value
        End With
    End If
End Sub

Now, if I select a string from the data validation dropdown in cell C5 the image in the shape changes.

Is that close to what you want to do?

 

[geopetlas]

Morning Rick,

I sent you a private message.

 

[RickXL]

Can you access this?

https://onedrive.live.com/redir?resid=681493C8FF2672D3!9272&authkey=!ALe5tDu5dReQyPo&ithint=file%2cxlsm


You may need to add a shape called "Oval 1" to the worksheet.

 

[RickXL]

Here is a picture:

 

[geopetlas]

OHHHH

I see why yours works and mine doesn't. I didn't explain myself clearly enough.

In your sheet you have the actual full path names as the data validation list.

In mine I have as the data validation list, Cessna, Piper, Beechcraft....
Then on sheet2 I have a Table Array, with Cessna, Piper, Beechcraft..... in A3:A...
Then in AU3:AU.. resides the actual full path names.
That is why it seems to me that mine doesn't work.
I was using VLookUp to get the path names.

Iin my actual worksheet each item in the validation list has 47 associated items that need to be used in calculations performed in Sheet1.

Sooooooooooo, Rick, working your magic can you can you fix it so this works where Cessna, Piper, Beechcraft ,etc are the validation list and then get the path names which are found using VLookUp to then fill the shape?

George