Learn Excel – VBA Insert Picture Bug – Podcast 2214

Learn Excel – VBA Insert Picture Bug – Podcast 2214

Learn Excel from MrExcel Podcast, Episode
2214: Really Annoying VBA Bug When You’re Trying to Insert a Picture. Alright. So, hey, this started happening in Excel 2010. I just got burnt by it again recently. So I’m going to insert a picture in this workbook,
but I want to record that action so I can automatically do it. View, Macros, Recorder Macro, HowToInsertAPicture. Perfect. And I’m just going to insert a picture here:
Illustrations, Pictures, let’s choose one of our rocket photos and Insert. Alright, stop recording. Beautiful. Now, I’m going to get rid of that. I want to take a look at the macros, so Alt+F8,
HowToInsertAPicture, Edit, and it says is this: ActiveSheet.Pictures.Insert and then
the path to the picture. Alright. Yeah, that sounds good. And, in fact, we should be able to run this. So Alt+8, HowToInsertAPicture, and Run, and
we get the picture– that’s beautiful. Until I save this workbook and let you download
it or send it to someone else, and then the picture doesn’t show up at all– all I get
is a red X saying, Hey, we can’t find the picture anymore. Like what do you mean you can’t find the picture? I asked you to insert a picture, not a link
to the picture. But starting in Excel 2010, this recorded
code is actually inserting a link to the picture. And if I open this workbook somewhere on a
computer that doesn’t have access to this drive and that picture: red X. Super annoying. Alright. So, for some reason, in Excel 2010, the new
thing to do, is instead of ActiveSheet.Pictures.Insert you do ActiveSheet.Shapes.AddPicture. Alright. And we can still specify a file name, but
then these extra arguments that we have: LinkToFile=msoFalse– in other words, don’t create the stupid red
link– and then, SaveWithDocument:=msoTrue– which means, actually put the darn picture
in there and they can specify where it’s supposed to be– the left, the top… Now, how do we figure out the height and the
width? Alright. Well, we want to resize this proportionally,
right? So I’m going to hold down the Shift key, like,
get this back to less than one screen full of data, maybe like that right there. So that’s my goal. I want to insert the picture and have a beam
that size with that selected. I’ll come back to VBA Alt+F11, Ctrl+G for
the immediate window, and I’ll ask for: ? selection.width– so that’s question mark, space, selection
dot width and a question mark, selection dot height (? Selection.height). Alright, and that tells me about 140 and 195–
so the width, 140, and 195, like that. Get rid of the immediate window, and then
here we’ll delete this and run the code, and it actually inserted it. It’s the right size, it’ll be able to be open
when you download this, or I download this, if you don’t have access to the original picture. I get it, things change, they had to change
the code. But the fact they didn’t update the macro
recorder, and the macro recorder was giving us the bad code, that doesn’t work. That’s super annoying. Well, hey to learn more about macros check
out this book, Excel 2016 VBA Macros, by Tracy Syrstad and myself. We actually have a version of this for every
version going back to excel 2003. So, whatever you have, provided it’s Windows,
there’s a version for you. Alright, wrap-up today– it’s my problem. I recorded code to insert a picture and it’s
creating a link to the picture, so anyone else I send the workbook to can’t see the
picture. Instead, I’m using the Macro recorder that
does ActiveSheets.Shape.Picture, use this new ActiveSheet.Shapes.AddPicture. Or we can specify LinkToFile, no; save with
document, yes; and you’ll be good to go. Well, hey, thanks for watching, I’ll see you
next time for another netcast from MrExcel.

Only registered users can comment.

  1. If you like this tip, please Subscribe to the channel. To read the article that matches this video, go to https://www.mrexcel.com/excel-tips/vba-insert-picture-bug/

  2. Speaking of bugs… does the “double-click” paint brush (copy formatting) not work in Office 365 for a reason or is it an oversight?

  3. Hello Sir,
    I have one question for you about summary function in Excel. I have one sheet, 1-8 tables where a salesman can fill the quantities in a row.This filled lines must be transferred to a new summary table.
    Thank you

  4. Bill – Is it possible to create a mirror effect of an image with excel VBA something similar which can be done in powerpoint?

  5. Hi Mr excel

    I have code below but I can't run this code
    Can you help me

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim shp As Shape

    If Intersect(Target, [A:A]) Is Nothing Then Exit Sub

    If Target.Row Mod 20 = 0 Then Exit Sub

    On Error GoTo son

    For Each shp In ActiveSheet.Shapes

    If shp.Type = msoPicture And shp.TopLeftCell.Address = Target.Offset(0, 4).Address Then shp.Delete


    If Target.Value <> "" And Dir(ThisWorkbook.Path & "" & Target.Value & ".jpg") = "" Then

    'picture not there!

    MsgBox Target.Value & " Doesn't exist!"

    End If

    ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "" & Target.Value & ".jpg").Select

    Selection.Top = Target.Offset(0, 4).Top

    Selection.Left = Target.Offset(0, 4).Left

    With Selection.ShapeRange

    .LockAspectRatio = msoFalse

    .Height = Target.Offset(0, 4).Height

    .Width = Target.Offset(0, 4).Width

    End With

    Target.Offset(1, 0).Select


    End Sub

  6. Bill, or anyone else!? I have a problem, that's kinda on this topic. I insert a picture exactly the same way (after exactly the same problem with the red X) but then try to position setting the .Top and .Left values based on a give cells, .top and .left values (the cell can be different each time the code is run). I debugged, and confirmed all values match, however the picture appears 2 cells lower? I have tried setting the position both at object creation, and after, and neither makes a difference. If you have any ideas I will be extremely grateful!! Many Thanks in advance.

Leave a Reply

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