Replacing a picture on an excel worksheet instead of adding a picture

Moonbeam111

Board Regular
Joined
Sep 24, 2018
Messages
56
Office Version
  1. 365
  2. 2010
Hey everyone. I have a picture code and what I want it to do is replace the picture instead of put it on top of it. Here is the code

VBA Code:
Sub Button396_Click()
Dim myR As Range
Set myR = Range("C11:J20")


myR.Select
 Dim picname As String

 picname = "" & Range("F7") & ".png" 'Link to the picture
 
 With ActiveSheet
   Set Picture = .Pictures.Insert(picname)
   With Picture
        With .ShapeRange
          .LockAspectRatio = msoFalse
          .Width = 700
          .Height = 150
          .ZOrder msoSendToBack
        End With
   End With
End With
 
Exit Sub
 


End Sub



The picture I need is currently taken from one of the folders in my hard drive and automatically put in excel via a worksheet change event by matching the name of the file to the name of the excel cell. However, as mentioned it doesn't replace the picture it just puts it on top and I don't like to have to delete the picture every time before i put in a new one in its spot.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try adding to your code the lines marked ++
VBA Code:
 With ActiveSheet
   On Error Resume Next                         '++
        .Shapes("ZC_Pic").Delete                '++
   On Error GoTo 0                              '++
   Set Picture = .Pictures.Insert(picname)
   With Picture
        .Name = "ZC_Pic"                        '++
        With .ShapeRange
          .LockAspectRatio = msoFalse
          .Width = 700
          .Height = 150
          .ZOrder msoSendToBack
        End With
   End With
End With
You need to remove manually the old images before start working with the new code
 
Upvote 0
Solution
Try adding to your code the lines marked ++
VBA Code:
 With ActiveSheet
   On Error Resume Next                         '++
        .Shapes("ZC_Pic").Delete                '++
   On Error GoTo 0                              '++
   Set Picture = .Pictures.Insert(picname)
   With Picture
        .Name = "ZC_Pic"                        '++
        With .ShapeRange
          .LockAspectRatio = msoFalse
          .Width = 700
          .Height = 150
          .ZOrder msoSendToBack
        End With
   End With
End With
You need to remove manually the old images before start working with the new code
That code works great for the most part but I have a couple problems.

1. One section of my worksheet puts one image directly on top of another. When I execute the code for that section it deletes the previous picture (as expected) but it also deletes the background image below it. Is there a possible fix for something like that?

2. How would I be able to 'center' align the images when placed in my worksheet? The code provided seems to left align them.

The code provided works pretty well! Just have those two considerations. Thanks.
 
Upvote 0
The suggested code delete an image whose name is "ZC_Pic", and it's unlikely that the background picture has this same name; so I don't understand why the background picture get deleted by that code.
As far as "centering", where should the image get centered?
 
Upvote 0
I think it's because I change the background image a lot of times in the same place (Range C11:J20). When i execute suggested code it will delete the background image because it is already named "ZC_Pic". I think a solution could be to have the files named something else upon initial insertion into the worksheet. Is it possible to get a different shape name everytime a new picture gets inserted? And it doesn't have to be fancy, simply ZC_Pic(1), then ZC_Pic(2), will do (as long as its not the same each time.) (Maybe it could even have a name based on cell reference).

For the question: I would like the image that supposed to go in the middle of merged cell C22:C25 to be centered.
 
Upvote 0
I need to know the name of the picture otherwise it could be tricky to remove the previous one when a new one will be inserted.
If inserting the background image is a one-time process then insert that picture and call it BCG_Pict (or anything different then ZC_Pic)

As far as the centering, do we have to keep the Width and Height you assigned or do we have to resize the picture according C22:C25 dimensions?
 
Upvote 0
I need to know the name of the picture otherwise it could be tricky to remove the previous one when a new one will be inserted.
If inserting the background image is a one-time process then insert that picture and call it BCG_Pict (or anything different then ZC_Pic)

As far as the centering, do we have to keep the Width and Height you assigned or do we have to resize the picture according C22:C25 dimensions?
I'm not sure if knowing the name of one image will help. You see the picture could be any in a whole folder of images. It's inserted into the worksheet based on the name of cell F7 (if the actual filename matches the cell name in F7). Its not even really a background image. Its just set behind the image I want on top. Thats why this line of code is there: .ZOrder msoSendToBack. If it helps lets say the name of the image set behind is named "Background_Image_1".

Please resize according to C22:C25 dimensions (retain aspect ratio). The width of that column is 116 pixels if that helps.
 
Upvote 0
I don't follow you...

The following code insert on the active worksheet a picture whose filename is in Cell F7, setting it in range("C22").MergeArea (ie C22 and other cells merged with C22), keeping the aspect ratio, adjusting the height according the target area height (the width will be automatically scaled to preserve the image aspectratio), and centered horizontally with column C
VBA Code:
Sub Button396_Click()
Dim myR As Range
''Set myR = Range("C11:J20")


''myR.Select
 Dim picname As String

 picname = "" & Range("F7") & ".png" 'Link to the picture

 With ActiveSheet
   On Error Resume Next                         
        .Shapes("ZC_Pic").Delete                
   On Error GoTo 0                              
'   Set Picture = .Pictures.Insert(picname)
    Set Picture = ActiveSheet.Shapes.AddPicture(picname, False, True, PosizLeft, PositTop, True, True)
    
Dim cPos As Range

   Set cPos = Range("C22").MergeArea
   With Picture
        .Name = "ZC_Pic"                        '++
        .LockAspectRatio = msoTrue
        .Height = cPos.Height
'        If .Width > cPos.Width Then .Width = cPos.Width     '***
        .Top = cPos.Top + cPos.Height / 2 - Picture.Height / 2
        .Left = cPos.Left + cPos.Width / 2 - Picture.Width / 2
'        .ZOrder msoSendToBack
   End With
End With
 
End Sub
I used .AddPicture because .Pictures.Insert would set a "link" to the image (you could not view the image if open the workbook on a second computer)
You'll notice that I gave priority to the Height of the image, ie we force the image to stay with the heigth of the range; this might result in the image beeing larger than the interval. If this is a problem then uncomment the instruction marked "***": it will reduce the image width to the width of the interval (and shrink it vertically)

If you need additional features then try adding them to that code, or specify which are these features amd maybe we will be able to help

Remember that when I say "the name of the image" I refer to the name we assign to the shape when inserted in out worksheet; the above code say that we assign the name "ZC_Pic", and I use this name to kill the previous picture before inserting the new one. This name has nothing in common to the filename of the image.
 
Upvote 0
I got what I needed using your first code. I left the background image to always be named "ZC_Pic" but then changed the top image to always be named "ZC_Pic(1)". That way the front image doesn't erase the back when called. I'm silly for not thinking of that sooner. Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top