This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Add a Pop-up Picture to Multiple Cells
Problem: I gave this book to my manager for Bosses’ Day. He saw “Add a Pop-up Picture of an Item in a Cell," and wants you to add pictures to dozens of cells. Adding pictures is one of the most tedious tasks in Excel. Is there an easy way?
Strategy: You can use a VBA macro to speed up a lot of jobs. I would never attempt this particular task without one, especially because this macro is so simple.
Figure out how to map the item numbers in the worksheet to your folder of pictures. In this case, the pictures are stored in C:\qimage\. The file name is the letters “QI", the part number, then “.jpg". You will have a different folder and likely a different prefix or suffix after the part number. Edit that line of the macro below.
1. Open your workbook in Excel.
2. Type Alt+F11 then Insert, Module.
3. Enter these few lines of code in the VBA Editor.
For Each cell In Selection
MyPic = “C:\Qimage\QI" & cell.Value & “.jpg"
.Shape.Height = 300
.Shape.Width = 300
4. Press Alt+Q to return to Excel.
5. Select the dozens of cells where your manager wants pictures.
6. Run the macro. Pictures will be added to all the cells in the selection.
Additional Details: For the complete guide to learning VBA, check out VBA & Macros for Microsoft Excel 2010 (ISBN 978-0789743145 ) from Que Publishing.