VBA Picture aspect ratio

[bardern88]

Hi, I've got a macro to work to look up pictures and pull them through, the problem is it's automatically filling the shape with the picture but I would like the original picture to remain in the same aspect and fit the shape.

I believe I need to use Lockaspectratio = true, but can't seem to get it to work anywhere.

any help greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)




If Target.Address = "$B$7" Then


On Error Resume Next




        newpic = "Y:\Packing\Excel Packing Template\Pictures\" & Range("E8").Value
      
        newpic1 = "Y:\Packing\Excel Packing Template\Pictures\" & Range("E9").Value
    
        newpic2 = "Y:\Packing\Excel Packing Template\Pictures\" & Range("E7").Value
        
        Target.Comment.Shape.Fill.UserPicture newpic1
    
      
ActiveSheet.Shapes.Range(Array("PICT1")).Insert.UserPicture.newpic
On Error Resume Next
ActiveSheet.Shapes.Range(Array("Picture 1")).Fill.UserPicture.newpic1
On Error Resume Next
ActiveSheet.Shapes.Range(Array("Picture 4")).Fill.UserPicture newpic1
On Error Resume Next
ActiveSheet.Shapes.Range(Array("Rectangle 1")).Fill.UserPicture newpic




ActiveSheet.Shapes.Range(Array("Forms.Image.1")).Fill.UserPicture newpic2
ActiveSheet.EMBED("Forms.Image.1", "").Fill newpic2




End If




End Sub