John asks if there is a way to automate the process from Episode 322 of adding pop-up pictures to a cell. Today's Episode 1108 takes a look at the VBA code to add pop-up pictures to many cells.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Alright, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today's question from John: talking about episode 322, isn't that a long time ago, where I showed how to add a comment that has a pop-up picture in a cell.
John says: hey, I have a whole bunch of these I have to do, very tedious to do it one at a time, can we do a macro?
Well, I tried it, I turned on the macro recorder and said AddPicsRecorded, and I went through the steps to add a picture to one cell.
So that's a New Comment, Backspace through the text, choose the comment and then Ctrl+1 to format and then we come here.
Colors and lines, poping this drop-down up, choose Fill Effects, where we choose Picture.
All right, Select Picture… Now, for this particular application, all of these SKUs have a picture in a particular folder.
So it's under seek and “qimage”, and then it starts with “qi” and the SKU.
So J45271, bingo, there it is.
Click Insert, click OK, click OK and we'll click away.
And now I can stop recording, well, see how this works now: when I hover over the red triangle, the picture pops up, very cool, right?
Hey, let's go take a look at that code.
So come back here to the Developer tab, Visual Basic and wow, they record a lot of stuff here.
There's the AddComment and then they go through and change the font, which is kind of silly, because I got rid of all of that text.
And then use Selection.ShapeRange.Fill.UserPicture, there you go.
Ok, now an interesting thing here: that's the recorded code, I actually went back to the book “Learn Excel ‘97 through 2010 from MrExcel” and out of 377 tips in this book, there's VBA and exactly one tip.
And it's the VBA that shows how to solve this problem.
The code that is in the book is much simpler than the recorded code.
Ok, so: For Each cell In Selection we're going to select the range of SKUs, figure out what the picture is, here's where I've hardcoded it: “C:\qimage\qi” & cell.Value & “.jpg”.
Very simple.
With cell, cell as an object variable, as we go through each item in the selection, we'll have an object variable called cell.
Cell.AddComment, and then then .Shape, not ShapeRange, .Fill.UserPicture and there's our variable.
So let's give it a try, we'll come back here, we'll select the rest of the SKUs, will run that code.
So macro AddPics, click run, bam, look how fast that was.
Added red triangles throughout, as we hover we can see the picture for each individual SKU, isn't that cool?
Hey, John, great question, how luckily that one was in the book.
So we'll send you back, for anyone who wants to see how to add one to episode 322, where we went through probably a bit slower, and then, for anyone who wants to see how to do that, of course the AddPics.
And by the way, in case I haven't mentioned it, you can always download that book for free.
Now, just go to mrexcel.com/previewsig.html and you can take a look at the book, figure out if you like it or not.
If you like it, go out and buy it, if you don't – yeah – would you lose 60 seconds to download the whole book.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Today's question from John: talking about episode 322, isn't that a long time ago, where I showed how to add a comment that has a pop-up picture in a cell.
John says: hey, I have a whole bunch of these I have to do, very tedious to do it one at a time, can we do a macro?
Well, I tried it, I turned on the macro recorder and said AddPicsRecorded, and I went through the steps to add a picture to one cell.
So that's a New Comment, Backspace through the text, choose the comment and then Ctrl+1 to format and then we come here.
Colors and lines, poping this drop-down up, choose Fill Effects, where we choose Picture.
All right, Select Picture… Now, for this particular application, all of these SKUs have a picture in a particular folder.
So it's under seek and “qimage”, and then it starts with “qi” and the SKU.
So J45271, bingo, there it is.
Click Insert, click OK, click OK and we'll click away.
And now I can stop recording, well, see how this works now: when I hover over the red triangle, the picture pops up, very cool, right?
Hey, let's go take a look at that code.
So come back here to the Developer tab, Visual Basic and wow, they record a lot of stuff here.
There's the AddComment and then they go through and change the font, which is kind of silly, because I got rid of all of that text.
And then use Selection.ShapeRange.Fill.UserPicture, there you go.
Ok, now an interesting thing here: that's the recorded code, I actually went back to the book “Learn Excel ‘97 through 2010 from MrExcel” and out of 377 tips in this book, there's VBA and exactly one tip.
And it's the VBA that shows how to solve this problem.
The code that is in the book is much simpler than the recorded code.
Ok, so: For Each cell In Selection we're going to select the range of SKUs, figure out what the picture is, here's where I've hardcoded it: “C:\qimage\qi” & cell.Value & “.jpg”.
Very simple.
With cell, cell as an object variable, as we go through each item in the selection, we'll have an object variable called cell.
Cell.AddComment, and then then .Shape, not ShapeRange, .Fill.UserPicture and there's our variable.
So let's give it a try, we'll come back here, we'll select the rest of the SKUs, will run that code.
So macro AddPics, click run, bam, look how fast that was.
Added red triangles throughout, as we hover we can see the picture for each individual SKU, isn't that cool?
Hey, John, great question, how luckily that one was in the book.
So we'll send you back, for anyone who wants to see how to add one to episode 322, where we went through probably a bit slower, and then, for anyone who wants to see how to do that, of course the AddPics.
And by the way, in case I haven't mentioned it, you can always download that book for free.
Now, just go to mrexcel.com/previewsig.html and you can take a look at the book, figure out if you like it or not.
If you like it, go out and buy it, if you don't – yeah – would you lose 60 seconds to download the whole book.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
