Bulk insert images and want to lock aspect ratio

tangsunx

New Member
Joined
Apr 6, 2019
Messages
3
Hi,

May I know how to lock aspect ratio by editing below code?


Code:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'}</style>Sub InsertPicFromFile()
    Dim xRg As Range
    Dim xCell As Range
    Dim xVal As String
    On Error Resume Next
    Set xRg = Application.InputBox("Please select file path cells:", "Insert Imagesl", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xVal = xCell.Value
        If xVal <> "" Then
            ActiveSheet.Shapes.AddPicture xCell.Value, msoFalse, msoTrue, _
            xCell.Offset(0, 1).Left, xCell.Top, xCell.Height, _
            xCell.Height
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Thanks!
 

Some videos you may like

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.

ravisingh

New Member
Joined
Apr 5, 2019
Messages
46
Try replacing the operative part of the code as under
Rich (BB code):
ActiveSheet.Shapes.AddPicture xCell.Value, msoFalse, msoTrue, xCell.Offset(0, 1).Left, xCell.Top, -1, -1

This will paste the pic in original size - then you can get the dimensions of the newly pasted pic, determine the aspect ratio and then scale up/scale down the size as per your requirement while keeping the aspect ratio intact. This will need additional code.
 
Last edited:

tangsunx

New Member
Joined
Apr 6, 2019
Messages
3
Thanks!

May I know how to add additional code?
For example, I would to set the image height=100 or just fit to the cell in aspect ratio.
 

ravisingh

New Member
Joined
Apr 5, 2019
Messages
46
You can programatically get the height and width of the picture with pic.height and pic.width - lock the aspect ratio with LockAspectRatio method...do many things....just read the VBA help - the code you have posted indicates that you can easily do this....
 

ravisingh

New Member
Joined
Apr 5, 2019
Messages
46
If you wish to fit it in the cell height, set the height of the embedded pic to cell height while keeping LockAspectRatio=True
e.g.
selection.shaperange.lockaspectratio=True
selection.Height=xcell.RowHeight

This will fit to cell height and proportionately set the width automatically.
Note: The sample code assumes the object is selected...modify the code if needed.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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