Aspect ratio of picture changing upon cell height change

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
269
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
In my workbook, I give the user the ability to insert a picture file into one of the worksheets. I have code that does this for them, so the sheet can remain protected the rest of the time. I also allow them to change the height of one of the rows in that sheet, in case they need a bit more room for their picture.

I set the aspect ratio of the picture to be locked in my code, but if the picture fully extends over the row that they change the height on, the picture gets longer or shorter as the row height is changed. I do not want this to happen. I do want them to be able to adjust the size of the picture, however, so I have to leave the sheet unprotected during this process.

Any idea how I can stop the aspect ratio from changing when the row height changes?

Here's the code I use to insert a picture into the worksheet:

Code:
Dim Pic As Shape

Set Pic = ThisWorkbook.ActiveSheet.Shapes.AddPicture(PicPathAndName, _
linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=0, Top:=0, Width:=-1, Height:=-1)

With Pic
    .LockAspectRatio = msoTrue
    .Top = ThisWorkbook.ActiveSheet.Cells(5, 9).Top
    .Left = ThisWorkbook.ActiveSheet.Cells(5, 9).Left
    .Placement = 1
End With

Note: The variable PicPathAndName is determined by some other code, depending on if the user is on Windows or Mac. I didn't include it here because the picture file is inserted into the sheet with no problem.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Code:
With Pic
  .Placement = xlFreeFloating
  '...
 
Upvote 0

Forum statistics

Threads
1,203,269
Messages
6,054,482
Members
444,727
Latest member
Mayank Sharma

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
Back
Top