Auto-Inserting Picture or Image

[harishs]

Dear Experts,

Need your help...

Code in the attached sheet helps me to insert a picture from a folder based on the cell value (A1)...

however when I change the value it is not replacing but it is inserting a new picture...

How can I rewrite the existing picture...?
I want to replace/delete exsiting picture and add/insert new picture when value in A1 changes...

please help...

Regards,
Harish S

[Jakobshavn]

Modified to delete the old picture first:

[harishs]

Thanks a lot Jakobshavn... it worked fine...

[lokicl]

Hi Harish,

You have to capture the shape name while you are updating the image, then it will work.

Later you can hide the specified range from end user

Use this code:

Sub Chk_Insrt()
Dim fso As Object, MyDir As String, Nme As String, myPic As Object
Dim ws As Worksheet

Set fso = CreateObject("Scripting.FileSystemObject")
MyDir = "D:\New\" 'Change Directory
Nme = Sheets(1).[a1] & ".jpg" 'Change Filename
If fso.FileExists(MyDir & Nme) Then
Application.ScreenUpdating = 0
Set ws = Sheets(1) 'Change the target sheet

'--- Code Update-----
myshapename = ws.Range("k1").Value
ws.Shapes(myshapename).Delete
'end

Set myPic = ws.Pictures.Insert(MyDir & Nme)



With myPic
.Top = ws.[d5].Top
.Left = ws.[d5].Left
.ShapeRange.Height = ws.[d5].RowHeight * 4.2 '4.2 rows tall
End With

'--- Code Update-----
ws.Range("k1").Value = myPic.Name
'end

Set myPic = Nothing: Set ws = Nothing
Application.ScreenUpdating = 1
End If
Set fso = Nothing
End Sub


Regards,
Loki