Create Macro to Insert and Re-size picture.

[Jonny1990]

Hi, I would like to create a macro to allow me to easily insert, resize and center a picture in a selected cell, or range of merged cells.

I would like to select a cell, key in the assigned shortcut and then have the "Insert Picture" dialogue box appear. Once I have selected the picture I would like to insert I would then like it to be re-sized to height- 5.05cm and width 7.6cm and centered in the middle of the selected cell.

Any help anyone could give me on this would be greatly appreciated.

Thank you in advance!

 

[Domenic]

The following macro will prompt the user to select a picture, insert it into the active worksheet, and resize it within the active/selected cell...

Option Explicit

Sub InsertAndCenterPicture()


    Dim vFileName As Variant
    Dim oPicture As Shape
    
    If TypeName(ActiveSheet) <> "Worksheet" Then
        MsgBox "No worksheet is active!", vbExclamation
        Exit Sub
    End If
    
    vFileName = Application.GetOpenFilename( _
        FileFilter:="Pictures, *.gif;*.jpg;*.png", _
        Title:="Select Picture")
        
    If vFileName = False Then Exit Sub
    
    Set oPicture = ActiveSheet.Shapes.AddPicture( _
        Filename:=vFileName, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=1, _
        Top:=1, _
        Width:=Application.CentimetersToPoints(5.05), _
        Height:=Application.CentimetersToPoints(7.6))
        
    With oPicture
        .Left = ActiveWindow.RangeSelection.Left + (ActiveWindow.RangeSelection.Width - .Width) / 2
        .Top = ActiveWindow.RangeSelection.Top + (ActiveWindow.RangeSelection.Height - .Height) / 2
    End With
    
End Sub

If you would like to include other types of pictures from which to select, add them to the FileFilter for GetOpenFilename.

Hope this helps!

 

[Jonny1990]

Thanks Domenic!

I seem to have an issue though, in that when I open the document on another computer the size of the pictures that I have added using the macro change slightly, while the pictures that I add by simply clicking insert, pictures and re-size manually are unaffected.

Have you any idea why this would be?

Thanks again for your help it is very much appreciated!

 

[Domenic]

If you right-click the picture and check the actual size, have the width and height actually changed from 5.05 and 7.6, respectively?