I showed this a long time ago on an episode of TechTV. The VBA code that I used in Excel 2003 to add the pop-up pictures no longer works in Excel 2010. Today, with Episode #1386, Bill shows us how to update the code for use in Microsoft Excel 2010.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1386-VBA Picture Comments.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay! Now, this goes all the way back to when I used to be a regular guest on Call for Help, on Tech TV.
I showed how to do these pop-up pictures, and then I think in a subsequent blog post.
I talked about how to add those with VBA because think about if you have a price list with 50 items, you don't want to go through and do the steps over and over and over again.
What are the steps?
Here are the steps, okay!
So you have a cell and you have to insert a comment, so insert or is that preview and I already know.
Yep! You comment and you [ backspace ] through all the words in that comment, alright!
And you see right now, you have diagonal lines.
Need to click on the diagonal lines or press [ ctrL enter ], to select and make it dots.
All right, then we want to format that.
So, [ ctrl 1 ] under colors at the bottom of all the colors, there are fill effects and then picture and then select picture.
Insert pictures, click [ ok ], click [ ok ], click away.
When they hover, they get the picture, all right cool!
Now, VBA back in Excel 2003, there was one set of VBA, and that's no longer working.
Now, in excel 2010 and presumably 2007.
So, let's switch over to developer tab, here visual basic.
I'll try and make this smaller, actually what I'll do, is all I have a continuation character here.
It used to be that we could just go to the the cell comment and dot fill with the user picture method and specify what the picture was.
You see here, I'm actually building it.
So, I know that, it's always in this folder.
I'm using the value from the cell itself and then concatenated dot Jpeg and that used to work.
But now, in Excel 2010.
I can't get that to work anymore.
So, I turned on the macro recorder, and it's operating on the shape range.
That's new and unfortunately, I can't seem to get it to work without selecting the shape range, and I can't select the shape range, unless I make the comment visible.
So, what used to be a nice, little, two line macro has now become this big whole thing.
So, for each cell in the range, I had a comment.
The comment dot text, the text is nothing.
I make the comment visible, and then select the shape associated with a comment.
Then finally, I get to do selection dot shape dot range dot fill dot user picture.
Build the path and filename in to the picture and now I have the comment selected.
I don't want to do that.
So, I go back and select the original cell and then make the comment not visible anymore.
So, let's run this code, and we'll go back to Excel, you see that.
Now, we have a little red triangle indicators and for each item we have the pop-up picture.
All right! So, this is a repeat, we've done this before on the podcast.
But not in Excel 2010 and unfortunately the code that we did when we did it back in Excel 2003, back in the Tech TV days has changed.
So, got a note from Andrew.
Andrew was trying to do this work code wasn't working and just kind of updating the current situation...
Thanks to Andrew for sending that question in..
Thanks to you for stopping by, we'll see you next time for another netcast MrExcel.
Learn Excel from MrExcel podcast, episode 1386-VBA Picture Comments.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay! Now, this goes all the way back to when I used to be a regular guest on Call for Help, on Tech TV.
I showed how to do these pop-up pictures, and then I think in a subsequent blog post.
I talked about how to add those with VBA because think about if you have a price list with 50 items, you don't want to go through and do the steps over and over and over again.
What are the steps?
Here are the steps, okay!
So you have a cell and you have to insert a comment, so insert or is that preview and I already know.
Yep! You comment and you [ backspace ] through all the words in that comment, alright!
And you see right now, you have diagonal lines.
Need to click on the diagonal lines or press [ ctrL enter ], to select and make it dots.
All right, then we want to format that.
So, [ ctrl 1 ] under colors at the bottom of all the colors, there are fill effects and then picture and then select picture.
Insert pictures, click [ ok ], click [ ok ], click away.
When they hover, they get the picture, all right cool!
Now, VBA back in Excel 2003, there was one set of VBA, and that's no longer working.
Now, in excel 2010 and presumably 2007.
So, let's switch over to developer tab, here visual basic.
I'll try and make this smaller, actually what I'll do, is all I have a continuation character here.
It used to be that we could just go to the the cell comment and dot fill with the user picture method and specify what the picture was.
You see here, I'm actually building it.
So, I know that, it's always in this folder.
I'm using the value from the cell itself and then concatenated dot Jpeg and that used to work.
But now, in Excel 2010.
I can't get that to work anymore.
So, I turned on the macro recorder, and it's operating on the shape range.
That's new and unfortunately, I can't seem to get it to work without selecting the shape range, and I can't select the shape range, unless I make the comment visible.
So, what used to be a nice, little, two line macro has now become this big whole thing.
So, for each cell in the range, I had a comment.
The comment dot text, the text is nothing.
I make the comment visible, and then select the shape associated with a comment.
Then finally, I get to do selection dot shape dot range dot fill dot user picture.
Build the path and filename in to the picture and now I have the comment selected.
I don't want to do that.
So, I go back and select the original cell and then make the comment not visible anymore.
So, let's run this code, and we'll go back to Excel, you see that.
Now, we have a little red triangle indicators and for each item we have the pop-up picture.
All right! So, this is a repeat, we've done this before on the podcast.
But not in Excel 2010 and unfortunately the code that we did when we did it back in Excel 2003, back in the Tech TV days has changed.
So, got a note from Andrew.
Andrew was trying to do this work code wasn't working and just kind of updating the current situation...
Thanks to Andrew for sending that question in..
Thanks to you for stopping by, we'll see you next time for another netcast MrExcel.
