Change picture of a shape

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95
I there, following the instructions here : VBA insert picture as fill

But I can't change the image.
First, let me say what I need. I have a sheet with a table with the location of some images, on this format: C:\Users\MyName\Google Drive\Invoices\IMG_20200911_181558.jpg
I want to go for all image, like a command button "Next" and "Previous". And for that I have a image to show the current image select. I already have this code, but not working, because don't change the picture:
VBA Code:
Option Explicit
Sub new_image()

Dim ws As Worksheet
Dim imagePath As String
Dim imgLeft As Double
Dim imgTop As Double
Dim shp As Object

Set ws = ActiveSheet
imagePath = "C:\Users\MyName\Google Drive\Invoices\IMG_20200911_181558.jpg"
imgLeft = ActiveCell.Left
imgTop = ActiveCell.Top

Set shp = ws.Shapes.AddPicture(imagePath, msoFalse, msoTrue, imgLeft, imgTop, -1, -1)
shp.Name = "img_invoices"

End Sub

Sub change_image()
With ActiveSheet.Shapes("img_invoices").Fill
    .Visible = msoTrue
    .UserPicture CStr([j53])                    ' cell J53
    .TextureTile = msoFalse
    .RotateWithObject = msoTrue
End With

End Sub

The sub change_image was made by @Worf. So If I put another link on cell J53 like: C:\Users\MyName\Google Drive\Invoices\IMG_test.jpg, the imagem don't change.
If someone has another solution to make this work, please let me know, for now I need help on this..
I tryed Image Object and used LoadPicture but the application got so laggy! So slow!
Thank you in advance.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95
I got it! An image cannot has fill, because it isn't visible! So I just have to use a textbox! ;) If there is another method please let me know!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You could use a rectangle shape, that's what the code Worf wrote was for.
Alternatively you could delete the picture & load a new one.
 

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95
You could use a rectangle shape, that's what the code Worf wrote was for.
Alternatively you could delete the picture & load a new one.
I think I didn't understand the code of Worf, and that's why I wasn't able to get it... The code is perfet!
Do you rather to use the rectangle instead of the text box?
Thank you @Fluff for helping!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I don't put pictures on a sheet, but if I did I would add them as a picture.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,348
Perhaps simply keep switching the shape with the new one by using single sub routine? Object library should actually have this method but since it doesn't have it, perhaps we can implement ours.

VBA Code:
Sub addImage()
Dim sht As Worksheet
Dim shp As Shape

    Set sht = ActiveSheet

    On Error Resume Next
    sht.Shapes("img_invoices").Delete
    On Error GoTo 0

    Set shp = sht.Shapes.AddPicture(sht.Range("J53"), msoFalse, msoTrue, ActiveCell.Left, ActiveCell.Top, -1, -1)
    shp.Name = "img_invoices"
End Sub
 

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95

ADVERTISEMENT

I don't put pictures on a sheet, but if I did I would add them as a picture.

I tried with Loadpicture(path), but the application did get laggy, even impossible to operate.

Perhaps simply keep switching the shape with the new one by using single sub routine? Object library should actually have this method but since it doesn't have it, perhaps we can implement ours.

VBA Code:
Sub addImage()
Dim sht As Worksheet
Dim shp As Shape

    Set sht = ActiveSheet

    On Error Resume Next
    sht.Shapes("img_invoices").Delete
    On Error GoTo 0

    Set shp = sht.Shapes.AddPicture(sht.Range("J53"), msoFalse, msoTrue, ActiveCell.Left, ActiveCell.Top, -1, -1)
    shp.Name = "img_invoices"
End Sub

Thank you @smozgur ! I'll give it a try! ;)
 

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95
@smozgur I could edit my previous post, but I think your solution deserves an explanation to be used instead of fill the shape.
This being said, I'm using your solution, although at first sight it wasn't exatly what I was looking for, but, using the fill of a shape increasies a LOT the size of the excel file, almost the size of the picture.
With your only incriesed 300k, which is exactly want I wanted! And turns out that you even notice the shape being deleted and created. This fits exactly what I was thinking.
So again, thank you for your solution!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

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