Juice Analytics

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

Thanks, Andreas. I chose a dark semi-transparent gray to be similar to the lightbox javascript implementation.

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.

Thank you Jon. How could I have missed that.

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.

Guilty as charged, Patrick. I didn’t even google it, because I was intrigued by this new technique.

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?

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.

dim cmt as Comment
dim sFile as String
dim rng as Range

set cmt = rng.AddComment
cmt.Text Text:=""
cmt.Shape.Fill.UserPicture sFile

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

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.

Del, good news!

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.

That is exactly what the lightbox script does.

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.).

I am Sorry, I cannot Download the File :Excel Lightbox.xls
Could You Send Me a Mail as Attachment?
Thank You Very Much!

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!

I have a similar idea to Jim. Can you show your code here Jim?

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

Hi,

I like your code, but how can I show an animated GIF image?

Thanks
Frank