This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

VBA Code to insert, move, delete and control pictures

VBA Code Snippets

VBA Code Snippets

It is not often that we have to control pictures or images within Excel. This post serves as a reference to cover most of the scenarios we’re likely to encounter when we do need to.

This code can be used in conjunction with the user defined function to insert and change pictures based on a cell value, which can be found here.

 

Adapting the code to your needs

It is unlikely that any of the code will meet your exact requirements. Every code snippet uses variables to hold either the image, the worksheet, a range or an object.  By changing those variables, the code can easily be changed and combined with other code snippets to meet your specific requirements.

 

Insert an image into a worksheet

The following code will insert an image into the active cell of the active worksheet, keeping the size of the original image.

Sub InsertImage()

Dim ws As Worksheet
Dim imagePath As String
Dim imgLeft As Double
Dim imgTop As Double

Set ws = ActiveSheet
imagePath = "C:\Users\marks\Documents\myImage.png"
imgLeft = ActiveCell.Left
imgTop = ActiveCell.Top

'Width & Height = -1 means keep original size
ws.Shapes.AddPicture _
    fileName:=imagePath, _
    LinkToFile:=msoFalse, _
    SaveWithDocument:=msoTrue, _
    Left:=imgLeft, _
    Top:=imgTop, _
    Width:=-1, _
    Height:=-1
 
End Sub

 

Loop through all images on a worksheet

The following code will loop through all the images on the active sheet.

Sub LoopThroughImagesOnWs()

Dim shp As Shape
Dim ws As Worksheet

Set ws = ActiveSheet

For Each shp In ws.Shapes

    If shp.Type = msoPicture Then
 
        'Do something to the image
        'Example, show message box
        MsgBox shp.Name & " is a picture"
 
    End If

Next shp

End Sub

 

Confirm if the selected object is a Picture

The code below will check if a specific object is a Picture.

Sub CheckIfSelectionIsPicture()

Dim thing As Object
Set thing = Selection

If TypeName(thing) = "Picture" Then
    MsgBox "Selection is a picture"
Else
    MsgBox "Selection is NOT a picture"
End If

End Sub

 

Rotate images

The following code rotates the image by a specific amount

Sub RotateImageIncremental()

Dim myImage As Shape
Dim rotationValue As Integer

Set myImage = ActiveSheet.Shapes("Picture 1")
rotationValue = 45

'Rotate the image by the amount specified by the rotationValue
myImage.IncrementRotation (rotationValue)

End Sub

The following code rotates the image to a specific amount.

Sub RotateImageAbsolute()

Dim myImage As Shape
Dim rotationValue As Integer

Set myImage = ActiveSheet.Shapes("Picture 2")
rotationValue = 90

'Rotate the image to the amount specified by the rotationValue
myImage.rotation = rotationValue

End Sub

Advertisement:

 

Set image position to the center of a cell

An image is positioned based on the top and left of that image. The following code will set the position so that it appears centered within a specific cell.

Sub CenterInCell()

Dim myImage As Shape
Dim cellLocation As Range

Set myImage = ActiveSheet.Shapes("Picture 1")
Set cellLocation = ActiveSheet.Range("B4")

myImage.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2)
myImage.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2)

End Sub

 

Flipping an image horizontally or vertically

Flip the image horizontally:

Sub FlipImageHorizontal()

Dim myImage As Shape
Set myImage = ActiveSheet.Shapes("Picture 1")

myImage.Flip msoFlipHorizontal

End Sub

Flip the image vertically:

Sub FlipImageVertical()

Dim myImage As Shape
Set myImage = ActiveSheet.Shapes("Picture 1")

myImage.Flip msoFlipVertical

End Sub

 

Resize an image

The code below locks the aspect ratio, therefore resizing the width or height will maintain the proportions of the image.

Sub ResizeImage()

Dim myImage As Shape
Dim imageWidth As Double

Set myImage = ActiveSheet.Shapes("Picture 1")
imageWidth = 100

myImage.LockAspectRatio = msoTrue
myImage.Width = imageWidth

End Sub

When setting the aspect ratio to msoFalse, the height and width operate independently.

Sub ResizeImage()

Dim myImage As Shape
Dim imageWidth As Double
Dim imageHeight as Double

Set myImage = ActiveSheet.Shapes("Picture 1")
imageWidth = 100
imageHeight = 50

myImage.LockAspectRatio = msoFalse
myImage.Width = imageWidth
myImage.Height = imageHeight

End Sub

 

Changing Z-Order

The image can be moved forward or backward within the stack of objects (known as the Z-Order).

Sub ChangeZOrderRelative()

Dim myImage As Shape

Set myImage = ActiveSheet.Shapes("Picture 1")

myImage.ZOrder msoBringForward

'Alternative send backward
'myImage.ZOrder msoSendBackward

End Sub

The Z-Order position cannot be set directly. First send the image to the back, then move the image forward with a loop.  Continue looping until the image reaches the correct Z-Order Position.

Sub ChangeZOrderAbsolute()

Dim myImage As Shape
Dim imageWidth As Double
Dim imageZPosition As Integer

Set myImage = ActiveSheet.Shapes("Picture 1")
imageZPosition = 3

'Force z-order to zero then bring forward
myImage.ZOrder msoSendToBack

Do While myImage.zOrderPosition < imageZPosition

    myImage.ZOrder msoBringForward

Loop

End Sub

 

Set the background image

The background image appears behind the cells in the spreadsheet.

Sub SetImageBackground()

Dim ws As Worksheet
Dim imgPath As String

Set ws = ActiveSheet
imgPath = "C:\Users\marks\Documents\myImage.png"

ws.SetBackgroundPicture fileName:=imgPath

'Remove the background image
'ws.SetBackgroundPicture fileName:="

End Sub


Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *