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. Is there an easy way?
Strategy: 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.
- Open your workbook in Excel.
- Type Alt+F11 then Insert, Module.
- Enter these few lines of code in the VBA Editor.
Sub AddABunch()
For Each cell In Selection
MyPic = "C:\Qimage\QI" & cell.Value & ".jpg"
With cell.AddComment
.Shape.Fill.UserPicture MyPic
.Shape.Height = 300
.Shape.Width = 300
End With
Next cell
End Sub
- Press Alt+Q to return to Excel.
- Select the dozens of cells where your manager wants pictures. 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 2013 (ISBN 978-0789748614 ) from QUE Publishing.