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

VBA Code to insert, move, delete and control pictures

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

Delete an image

The code below will delete a specific named picture.

Sub DeletePicture()

Dim myImage As Shape

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

myImage.Delete

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

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

Cropping

The code below crops an image based on the distance from the top, left, bottom or right.

Sub CropImage()

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

myImage.PictureFormat.CropLeft = 50
myImage.PictureFormat.CropTop = 50
myImage.PictureFormat.CropRight = 50
myImage.PictureFormat.CropBottom = 50

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

28 thoughts on “VBA Code to insert, move, delete and control pictures

  1. diegolc says:

    How can I use an aleatory picture from the worksheet to set its position relatively with a specific cell?

    I’m working on a protocol document where the user inserts lots of images and I want to have the last one centered in some specific cell by the time it is inserted.

    Instead of using the “Picture 3” of the Sub CenterInCell(), is it possible to use the counter reference?

    For example, everytime I run the code, a counter variable updates the number of the imagens already on the document and aplies the sets of dimension and position only for this last one.

    I don’t know if I made myself clear.

    • Excel Off The Grid says:

      To reference the last picture, replace this:

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

      with this:

      Set myImage = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

      For this to work consistently, I am assuming the pictures are the only shapes on the worksheet.

  2. Syed Hasnain Ahmed says:

    Hello Friend, I need your help as an expert, i hope you may be able to help me. I am making a simple form with just code, name, gender and picture.

    I can add values from my userform in cell easily even load picture in Image_control box. But i can’t add picture in cell directly from
    Image_control box.

    I am adding link of my file, kindly go through it and tell me what i am doing wrong.

    https://drive.google.com/file/d/1zuwz5mBa839oM7Gs8NGgPbaEnNV5Do4s/view?usp=sharing

    • Excel Off The Grid says:

      The simplest option I can think of is to also load the file path of the picture into a textbox. Then, when you put the data into the worksheet you can use the image file path.

  3. Mohiuddin says:

    Dear Sir
    I want to save picture in C drive folder from multi page userform & here is five image box . I want to save picture from every image box in different folder. Can you help me? I can’t write the code.

  4. Georgiy says:

    Hello. This is a good article. I have one question: how to stretch the picture to the specified range? Or how to get the height and width of the range to set the height and width of the picture?

  5. Pavithran says:

    I need help bro please i asked somany person from youtube nobody help.
    i want move picture folder to folder from userform textbox name .
    For example while type name or number in textbox1 move picture folder 1 to folder 2 workbook path please help

  6. Matthew says:

    Hi.
    Great article thank you. Can you advise what VBA code I should use to find the position attributes of a picture on a worksheet. I’m not sure how pictures are positioned on a worksheet ie whether they are positioned relative to cells / cell references or whether they are positioned according to an X, Y axis. However, I want the X, Y position references if they exist ! Thank you so much for any help you can give.

    • Excel Off The Grid says:

      Hi Matthew

      If you want the Top and Left, try this:

      MsgBox ActiveSheet.Shapes("Picture 1").Top
      MsgBox ActiveSheet.Shapes("Picture 1").Left
      

      Or if you want to know which cell the top left corner is in, then try this:

      MsgBox ActiveSheet.Shapes("Picture 1").TopLeftCell.Address
      
  7. Matthew says:

    Hi. I have a bunch of pictures (30+) on the ActiveSheet. I would like to position them according to some pre-determined positions ie I have the Top & Left positions along with the picture names. Rather than write the code for each individual picture, calling it and positioning it, I was wondering if a better approach would be to state my data into an array (Picture Name, Top Position, Left Position) and then somehow loop through each picture and position it according to the array values. But I’ve no idea if this is a good method and if it is, where to begin ! (I do know how to declare and populate an array). Any help would be gladly received. Thank you. Matthew

  8. Holger Hoffmann-Riem says:

    This is a wonderful article – it’s hard to find this kind of information on the web! There is one issue that I didn’t manage to understand: Let’s assume you have a worksheet that already has various images, and you then follow the instructions in the section “Insert an image into a worksheet”. Afterwards you want to follow “Set image position to the center of a cell”. That code uses

    Set myImage = ActiveSheet.Shapes(“Picture 1”)

    How can you use this if you don’t know the name that Excel has given to your picture? Or in other words: how can you find out the name that you need to select the desired picture? I haven’t found anything that helped me to understand this process – apart from some advanced Macros that I don’t understand. It would be wonderful if you could clarify this!

    Best wishes from Switzerland,
    Holger

    • Excel Off The Grid says:

      Create a variable to hold the picture. Then set the added picture to the variable.

      Dim myImage As Shape
      
      Set myImage = ws.Shapes.AddPicture( _
          Filename:=imagePath, _
          LinkToFile:=msoFalse, _
          SaveWithDocument:=msoTrue, _
          Left:=imgLeft, _
          Top:=imgTop, _
          Width:=-1, _
          Height:=-1)
      

      You can now set the attributes of the picture by using the variable name. For example:

      myImage.Left = 1000
      
  9. Gary Miles says:

    Thank you for your wonderful insight.
    I’m using an old excel 2003 because I’m familiar with it.
    What I want to do is to embed several pictures onto the one worksheet but have them all not visible by default.
    Then when certain actions happen on the worksheet make one become visible randomly.
    I’d like to give each picture it’s own name rather than default to Picture 1 …2…etc.
    I seem to be getting stuck at selecting the pictures and then editing various attributes like visibility and name .
    Any help on code snippets to get me up and running will be greatly appreciated from down under.

    • Excel Off The Grid says:

      Hi Gary,

      If you click on the picture, you can enter a new name in the name box and press return.
      The name box is to the left of the formula box. Using this, you can give pictures useful names, which will make writing the VBA code much easier.

  10. Andreas Kuhn says:

    Hello, I have a confusing problem copying images from one sheet to another. The images on the source-sheet have the same name as the worksheet. The named range (as the place where the picture is inserted) consists of several connected cells.

    Worksheets(Report).Shapes(Report).Copy
    Worksheets(Blattname).Paste Worksheets(Blattname).Range(Su_Bild)

    About 30 pictures are copied and pasted using a loop. I keep getting a 1004 error “The worksheet object’s paste method could not be executed.” with different images or insertion locations.

    Every time I start the procedure, I get one or more errors, but always with different images.

    Ironically, when I open the debugger and press F5, copying continues without a hitch until the next error.

    What is striking is that the first inserted image remains selected.

    I tried “Do Events” and even “Sleep 50” between copy and paste, t that doesn’t help either.

  11. Michael R says:

    If I were to want to store images in the Excel workbook so that they are embedded with the file and travel with it, is there a way with VBA to, say, change a current picture in my spreadsheet to one of the embedded images, and inversely, change the picture back? The goal is to create a sort of GUI for users in which sometimes I need a single image to fire a macro that toggles something, and so the image needs to change to reflect this.

    I am open to swapping embedded image files OR using wide images while only showing part of the image that has the appropriate graphic element in it. (Example, one image, 48h x96w, with “O” on the left and “X” on the right, and changing what the user sees.)

    Any insight?

    • Excel Off The Grid says:

      Hi Michael – there are lots of options to achieve what you want.

      The easiest is probably to make images visible/hidden as a way of swapping images. The following will hide Picture1 and display Picture2.

      ActiveSheet.Shapes("myPicture1").Visible = False
      ActiveSheet.Shapes("myPicture2").Visible = True

      Let me know if it works for you.

  12. Graeme Crawley says:

    Very useful stuff thanks. I have a need to produce lots of participation certificates and would like to use a picture back ground and overlay the participant name and some other details on the picture looping through the people from go to end
    Currently I have the people names written out to a cell then copied and pasted as a picture on top of the backgound picture – all good except one needs to remove the last person before pasting the words for the next person into the background picture. The macro finds the Words-as-Picture of the last person by selecting (say) Picture 12 and deletes it but when the next person is pasted the picture name changes from picture 12 to Picture 13 so next time one has to find picture 13 not 12, and delete that then picture 14 and so on. Yes I could track the picture numbers but it feels unreliable. Is there a way to rename each new pasted wording-picture to (say) picture 12 after pasting it into the backgound picture or paste it as Picture 12 rather than letting Xl simply increment it? The the macro would always find and delete picture 12 before pasting the new words as Picture 12 again.

    • Excel Off The Grid says:

      Hi Graeme – I think I would approach this differently.

      Use a linked picture. Check out the first example on this post, it might give you some ideas:
      https://exceloffthegrid.com/automatically-change-picture/

      Rather than an INDEX/MATCH, you could try an OFFSET function. You would then loop through a counter to move the OFFSET down by one row each time.

      Let me know if this doesn’t make sense.

  13. Doug says:

    What is the VBA code to change the Formula of a picture?
    I’m creating pictures that reference named ranges and would like to do it in bulk (hence VBA). Thanks.

    • Excel Off The Grid says:

      Here is an example code using Sheet1 and a shape called Picture 1

      Sheets("Sheet1").Pictures("Picture 1").Formula = "=A1"

Leave a Reply

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