looking to resize/lock images

zera

New Member
Joined
Sep 3, 2009
Messages
7
I'm looking to add in the lines to lock the inserted image ratio and specify a width of 5 inches. I'm not real sure how to go about this. Any help would be greatly appreciated.

Sub inserts a pic in a specified cell range if the cell contents equal the pic file name. All of the pics aren't the same size, so I would like to resize them with a locked image ratio to prevent distorting them. (5inch width, height to be determined by image ratio)
************************************************************************
Sub InsertPicComment()
Const sPath As String = "C:\Username\Profile Book\Drawing Images\8200\"
Dim cell As Range
Dim **** As String
Dim oCmt As Comment
For Each cell In Range("A1780:A1884")
With cell
If Len(.Text) Then
**** = sPath & .Value & ".jpg"

If Len(Dir(****)) Then
If .Comment Is Nothing Then
Set oCmt = .AddComment
Else
Set oCmt = .Comment
End If
With oCmt
.Text Text:="Contact Username to report errors"
.Shape.Fill.UserPicture ****
.Visible = False
End With
Else
MsgBox "Pic " & **** & " not found for cell " & .Address
End If
End If
End With
Next cell
End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Ian B

New Member
Joined
Jun 1, 2003
Messages
22
Zera,

If you click on an image, then right click it, you get the "Format Autoshape" option. Pick the "Size" tab. There's a check box to maintain aspect ratio, and you can enter the desired width - if you do this with the macro recorder on, would this do what you want?

2 other interesting things when resizing images by hand. Pick the corner 'handle', hold the shift key down and aspect ratio is maintained. The alt key gives you a snap to cell effect.

hth,

Ian
 

Forum statistics

Threads
1,136,331
Messages
5,675,156
Members
419,552
Latest member
jsanjur

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
Top