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

geopetlas

New Member
Joined
Feb 21, 2016
Messages
19
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.

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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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;
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:
Code:
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

New Member
Joined
Feb 21, 2016
Messages
19
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
 

geopetlas

New Member
Joined
Feb 21, 2016
Messages
19

ADVERTISEMENT

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
Code:
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
Code:
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.:confused:
George
Excel 2016
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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:
Code:
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

New Member
Joined
Feb 21, 2016
Messages
19

ADVERTISEMENT

Morning Rick,

I sent you a private message.
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Here is a picture:

Fill%20shape%20with%20picture.PNG
 

geopetlas

New Member
Joined
Feb 21, 2016
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top