400 error, picture insert, resize

**Code Flunkie** · 03-30-2010, 04:26 PM

I'm trying to write a macro to insert a picture into a selected range and resize and center it while keeping the aspect ratio. it doesn't seem to work and i can't figure out why. it would insert the picture and size it to about a 1/4 of the range and leave it in the top left. I disable the error trap and it's giving me a 400 error with no debugging options. can anyone see what i'm doing wrong in my macro?. any help would be greatly appreciated.

Chris

Please Login or Register  to view this content.

**dv8** · 03-30-2010, 07:56 PM

Hi Chris

I think this is what you want

Please Login or Register  to view this content.

**Code Flunkie** · 03-31-2010, 08:42 AM

wow works great, thanks a lot. just one more question

is it possible to delete any picture already in the selected range first before putting the new picture in?

Chris

**dv8** · 03-31-2010, 09:05 AM

Glad it helped Chris. I don't think there is a way to directly reference pictures within a range, although you could cycle through each shape within a worksheet, and determine whether any extents of a picture fall within the extents of the range, deleting as appropriate.
Dave

**Code Flunkie** · 03-31-2010, 10:43 AM

Found a way to do it, and put some error handling incase no range is selected. but now i'm having the problem that if you cancel putting in the new picture the old picture is still deleted. any way around this that the the old picture is deleted only if a new picture is selected?

Chris

ps. posted the revised code below.

Please Login or Register  to view this content.

**dv8** · 03-31-2010, 01:56 PM

Well, before this thread I didn't know about IIf, Intersect, and the top left / bottom right cell references for shapes, so I'm doing well!

Here's what you want, code tidied up and commented.

Please Login or Register  to view this content.

cheers
Dave

**Code Flunkie** · 03-31-2010, 02:26 PM

Wow, that is exactly what i was looking for, well written too. thanks for taking the time to help me out and i'm glad you learned something too.

Take care and thanks again

Chris