Lightboxing Images in Excel
By Chris Gemignani
November 22, 2006
Find more about:
excel
tools
I received an email out of the blue yesterday asking if its possible to put pictures in Excel comments:
I am working on a media report based on creative templates and wanted to give the end user easy access to a view of the creative and my thought was it would be neat to have the ability to build in a comment like function but with graphics.
As far as I know you can't put images in comments, but maybe we can do better. Web developers have developed a technique called Lightbox that was pioneered by Loresh Dhakar. When you click on a thumbnail the image opens full size in the center of your browser screen and the background is grayed out. Check out an example here.
Can we do the same in Excel? Absolutely. Download these two files to see how.
media.jpg (save to the root of your C:\ drive)
Here's a simple report in Excel Lightbox.
Clicking the Show button brings up the lightbox view. The image is dismissed by clicking on it.
This could be improved and extended in lots of ways. The image could be fetched from a URL. Thumbnails could be automatically generated. I'd love to see your ideas on how to extend this.
Subscribe & Browse
- Visitors Guide Our most popular posts and tools
- Subscribe via RSS or Email
- Browse by Topic, Date or Author
Explore our world
Recent Posts
- Designing a Better 'Federal IT Dashboard'
- 30 Great Visualization Resources in 30 Days
- Come Have Breakfast with Juice on July 15th
- Measures and Averages and Indexes, Oh My!
- Can familiarity trump usability?
- Chart Makeovers
- Meet Juice and Connect More Visually
- Follow us @JuiceAnalytics
- Better Know a Visualization: Motion Charts
- Memorable or Actionable or Both.
17 comments | Show all comments only the last 5 are shown
Andreas Lipphardt said:
Chris,
This is a very nice idea.
Maybe you could use a transparent light gray for the ShadowBox instead of the fill pattern. The fill pattern is a bit distracting
Andreas
Chris said:
Thanks, Andreas. I chose a dark semi-transparent gray to be similar to the lightbox javascript implementation.
Jon Peltier said:
"As far as I know you can’t put images in comments."
Sure you can:
Select a cell with a comment, go to Insert menu > Edit Comment. Select the comment (click edge of box to exit text-edit mode), go to Format menu > Comment, Colors and Lines tab, click Color dropdown, choose Fill Effects, Picture tab, Select Picture button, and browse to the image file.
Chris said:
Thank you Jon. How could I have missed that. ;-)
Patrick O'Beirne said:
"Thank you Jon. How could I have missed that. ;-) "
You're fortunate to have Jon as the expert, but even without that input Google is your friend:
http://www.contextures.com/xlcomments02.html#Picture
http://www.dailydoseofexcel.com/archives/2005/04/19/funky-comments/
etc
Thanks for the blog on graphics!
I'm interested in more on analytics in Excel such as cluster analysis.
Chris said:
Guilty as charged, Patrick. I didn't even google it, because I was intrigued by this new technique.
del c said:
Jon, great idea, but I have a question.
If I had a thousand pictures, and a description of their location in an Excel database, could I, or someone more competent at VBA than I, write a macro to automate the insertion of comments into a thousand cells, each with its appropriate picture?
Chris said:
Del,
Inserting picture comments is actually quite easy--a reader has sent me a wonderful example of how to do it. Now, inserting a thousand comment pictures might not be too wise, but here's the nub of how you do it.
<pre>dim cmt as Comment
dim sFile as String
dim rng as Range</pre>
<pre>
set cmt = rng.AddComment
cmt.Text Text:=""
cmt.Shape.Fill.UserPicture sFile</pre>
sFile is the path to the image, rng is the range you want to add the comment to. If you send me your email, I'll send you a sample spreadsheet.
Chris
del c said:
Of course: it's just occurred to me that the actual information itself would be held in the comments, not a reference to a file held safely elsewhere. Nevermind :-)
I'll stick with hypertext references, which is what I used the last time I wanted to use an Excel spreadsheet as a database while also letting my audience easily consult a picture of what the line they were reading was describing.
Now, if your lightbox script technique works to grab an image held on the same medium, but outside the spreadsheet, thus avoiding bloating the spreadsheet file size horribly, that would be what I was thinking of, which would clearly be more elegant than using comment boxes.
Chris said:
Del, good news!
<blockquote>
Now, if your lightbox script technique works to grab an image held on the same medium, but outside the spreadsheet, thus avoiding bloating the spreadsheet file size horribly, that would be what I was thinking of, which would clearly be more elegant than using comment boxes.
</blockquote>
That is exactly what the lightbox script does.
Jon Peltier said:
Patrick -
"Google is your friend"
Google is one of my best friends, one of my favorite programming tools. But it takes a little imagination, first in thinking of Google; then in thinking "I don't think I can put an image into a comment, but maybe it's possible"; then in thinking of lateral solutions, like the image in a comment vs. the lightbox approach seen here, and the tradeoffs inherent in each (filesize, vba or not, etc.).
SkyZ said:
I am Sorry, I cannot Download the File :Excel Lightbox.xls
Could You Send Me a Mail as Attachment?
Thank You Very Much!
Jim said:
Chris, I am enjoying playing with the lightbox code and am extending it to pick different pictures depending on the active cell selected. I have 125 jpeg files of marine invertebrates I want to pop up in a spreadsheet, each selected by the user by positioning to an active cell containing the name of the invertbrate. I can get the program to pick filenames from different locations to insert, but I cannot seem to move the location of the picture from row 3 column 2, even when I unlock everything and force new row and column positions for the insert. Any ideas? Thanks!
Joaquin said:
I have a similar idea to Jim. Can you show your code here Jim?
Ruoall Chapman said:
Hi,
Please help.
The sample files work perfectly, but as soon as I try to replicate it in Excel 2007 it doesnt work all that well.
The background shading does not appear and the buttons do not hide themselves when displaying the lightbox images.
Your comments or suggestions would be higly appreciated.
Thank you,
Ruoall Chapman
Frank said:
Hi,
I like your code, but how can I show an animated GIF image?
Thanks
Frank
Alex Wuethrich said:
Hi, the sample will work. but, if I have pics in the sheet, all pics will delete. Have you any ideas? Thx Alex
said:
Add a comment