MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Insert Picture Bug

June 06, 2018 - by Bill Jelen

VBA Insert Picture Bug

I needed to have an Excel macro insert a picture in Excel. I turned on the macro recorder, inserted the picture. But when I used that recorded code, the picture will not appear on any other computer. Instead, I get a red X where the picture should be.

The code used to work in Excel 2007 or earlier. But something changed in Excel 2010. When you run code to ActiveSheet.Pictures.Insert, Excel is *not* inserting the picture. Instead, it is inserting a link to the picture.

Of course, when you run the code on your computer, it appears like everything worked. The picture appears.

Looks like the macro worked.
Looks like the macro worked.

But when someone opens the workbook on another PC, they get a red X and a message that the picture may have been moved or renamed.

Red X instead of the image
Red X instead of the image

Well, of course the image is not on my Manager's computer. I did not ask for Excel to create a link to the picture. I asked for Excel to insert the picture. But the recorded code is inserting a link to the picture.

The solution is to switch to different code. By using Shapes.AddPicture, you can specify LinkToFile:=msoFalse. Here is the code to use:

Sub HowToInsertPicture()
    ' This code fails in 2010 or newer
    ' ActiveSheet.Pictures.Insert( _
    '    "C:\Users\Bill\Desktop\PhotoPrint\All Rocket\aWalg180422-3.jpg").Select
    NewFN = "C:\Users\Bill\Desktop\PhotoPrint\All Rocket\aWalg180422-3.jpg"
    ActiveSheet.Shapes.AddPicture(Filename:=NewFN, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=1, Top:=1, Width:=140, Height:=195).Select
End Sub

Watch Video

Download Excel File

To download the excel file: vba-insert-picture-bug.xlsm

Many times, the macro recorder in Excel records the wrong code. With the fix above, you can successfully insert images using VBA.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Excel is a tool in service of the people who have to live with the consequences."

Title Photo: Brigitta Schneiter on Unsplash

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.