Macro to automatically resize pasted images?

[justjerry]

I tried seeking help on ExcelForum.com but my post very quickly fell to the second page with no responses. Hopefully you all can help me out, it would greatly appreciated.

I am currently working on a project that requires me to screen capture web pages and then paste the images into a spread sheet, resized to 40% of their original size. Currently I have code in place that allows me to press Control E to resize a selected image. I would like to, instead, have the image automatically paste in at 40%. Either that or code that will paste, select, and then resize. I don't know how to select the pasted image and resize it using code though, as I'm not sure how to select the image once it's pasted.

Here is what I have so far, which works in that I have to select the pasted object and run the macro:

Sub resizeImages()

Selection.ShapeRange.ScaleWidth 0.63, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.63, msoFalse, msoScaleFromTopLeft

End Sub

THANKS!!


<!-- BEGIN TEMPLATE: bbcode_quote -->

 

[justjerry]

Bump - - Can anyone help me with this?

 

[R Pelle]

By no means do I know a lot of VBA but this is what I did.
I manually pasted some random pictures on Sheet 1

then created the following

Sub FortySize()
   Set myDocument = Worksheets(1)
   myDocument.Shapes.SelectAll
 
   Selection.ShapeRange.ScaleWidth 0.63, msoFalse, msoScaleFromTopLeft
   Selection.ShapeRange.ScaleHeight 0.63, msoFalse, msoScaleFromTopLeft
End Sub

and stepped through it with F8 and it resized all the images in 1 go.

See also http://msdn.microsoft.com/en-us/library/aa174305(v=office.11).aspx

 

[justjerry]

Re: Macro to automatically resize pasted images? SOLVED!

By no means do I know a lot of VBA but this is what I did.
I manually pasted some random pictures on Sheet 1

then created the following

Sub FortySize()
   Set myDocument = Worksheets(1)
   myDocument.Shapes.SelectAll
 
   Selection.ShapeRange.ScaleWidth 0.63, msoFalse, msoScaleFromTopLeft
   Selection.ShapeRange.ScaleHeight 0.63, msoFalse, msoScaleFromTopLeft
End Sub

and stepped through it with F8 and it resized all the images in 1 go.

See also http://msdn.microsoft.com/en-us/library/aa174305(v=office.11).aspx

Thank you for the help, that worked to an extent. I wanted something that would allow me to run the operation every time I pasted the image instead of at the end. I did find an answer online though, this is what I've come up with that works perfectly ... Control E pastes and resizes! :

Sub resizeImages()

'Selection.ShapeRange.ScaleWidth 0.63, msoFalse, msoScaleFromTopLeft
'Selection.ShapeRange.ScaleHeight 0.63, msoFalse, msoScaleFromTopLeft
ActiveSheet.Paste
With Selection
.Width = 350
.Height = 350
End With
End Sub

Thanks for your help!

 

[R Pelle]

You're welcome, thx for the feedback.