Need Help with this small code

[bluejax]

I'm new in here. And FYI I have no knowledge of VBA. I got this code from searching around on Google and then made few adjustments as far as I understood.

Its working perfectly. But then an error comes up if an image is renamed or deleted.

My Work: I have about 100 files from my office work. In which I have to insert about 20 photos per excel file. Before getting this code I was inserting manually and then adjusting the height and width by using the slider in the box already in the worksheet.

I got tired of it and my hands started to pain after few 10s files. Also if some thing goes wrong I get angry and close up the work. Then I searched up Google to find some thing to easy the work work. And I found this:


Sub Add_Picture()
'
' Macro recorded 9/7/01 by Terry Moffitt
'

'
Application.ScreenUpdating = False

'varible Picture1 is inserted down below - ***change both***
Picture = Application.GetOpenFilename("Picture,*.JPG,Picture,*.JPEG")
'edit "("Picture,*.*")" section to add or chanve visible file types

ActiveSheet.Pictures.Insert(Picture).Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 270
Selection.ShapeRange.Width = 270
Application.ScreenUpdating = True

End Sub

This code really made my work easy. But just then I was about to goto sleep. I thought to recheck random files and this is what I get.

After few minutes of going around I realised I have renamed the image after inserting in the sheet.

So my question is? Is there any way to modify this to prevent this from appearing. Because it will be a loss for me to do all this and in the end get this. Also I deleted few photos after inserting in the sheet.

 

[wigi]

Like this:

Sub wigi()

    ActiveSheet.Shapes.AddPicture(Application.GetOpenFilename("Picture,*.JPG,Picture,*.JPEG"), False, True, ActiveCell.Left, ActiveCell.Top, 270, 270).LockAspectRatio = msoFalse


End Sub

 

[bluejax]

Like this:

Sub wigi()

    ActiveSheet.Shapes.AddPicture(Application.GetOpenFilename("Picture,*.JPG,Picture,*.JPEG"), False, True, ActiveCell.Left, ActiveCell.Top, 270, 270).LockAspectRatio = msoFalse


End Sub

I didn't get the code. What will it do? Can you explain it.

I need two things to be made easy with this code. When I delete the photo it still shows in the sheet rather then giving error and when I rename a photo it shouldn't show me the error.

 

[wigi]

Did you test the code? That seems to me the first thing to do

 

[bluejax]

Checking..... Insert image, then save, close excel, delete image. Open again. Woaah!! Photos are there.

Thanks.. You are a life saver.

One more thing as you can see height and width are 270, the photos fit perfectly. But sometimes when I insert the photos the size becomes small why is that?

 

[wigi]

You're welcome!