Question regarding Macro for pop-up window with picture

**lilsnoop** · 07-19-2007, 07:02 PM

I'd sure appreciate anyone's help (if possible) reference creating a macro, which would allow me to click on a particular cell, which will have an ID number (1 through 34), each ID# is attributed to a particular employee. These ID# are located in my spreadsheet in columns B & G, and the ID #'s begin initially in B6 & G6 and continue downward to B34 & G34. These ID #'s are used in a vlookup type formula which covers 35 pages of this workbook. What I'd like to do is be able to click on any cell between B6& B34 (and/or the same in G6&G34), which has a particular ID # in it and have the photo of that particular employee designated with that ID# pop up in a window generated by the macro. I hope this makes sense! Any help would be greatly appreciated. My 1st sheet in my workbook is my "lookup" tab, which contains my ID# list and numbering sequence affiliated to each employee. So I'm not sure if I'd need to put the photograph of the employee in a column next to the employees name or have a folder designated with all my pictures in a certain directory, etc. Thanks for any suggestions if this in fact even possible...

**Leith Ross** · 07-19-2007, 07:20 PM

Hello Lilsnoop,

Saving the pictures in a folder is Ok if you aren't moving your workbook from computer tot computer. One way to have the pictures pop up in the cells when the mouse is on the cell is to place the picture in a comment for the cell. If this is of interest to you , I'll post the macro to read the pictures from a folder an load them into the cell comments.

Sincerely,
Leith Ross

**lilsnoop** · 07-19-2007, 07:23 PM

That would be great! I appreciate the help!

**Leith Ross** · 07-20-2007, 01:34 AM

Hello Lilsnoop,

If you post your workbook it will be both easier and faster for me to customize the macro. Can you do post it? Here is the basic macro code. This adds one picture at a time.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**royUK** · 07-20-2007, 03:28 AM

Have a look at Andy Pope's example

http://www.andypope.info/fun/picviewer.htm

**lilsnoop** · 07-20-2007, 07:05 AM

Leith I'm attaching a smaller version of my workbook. This example has my lookup table and four other employee sheets. My 2nd tab (worksheet) titled "Delmar Smith" has data entered in columns B6 & G6, representing employees identified in the lookup table. This I believe should show you my intent. The employees id'd in B6 & G6 per their individual ID# covered an 8 hr shift for "Delmar Smith" and each employee wages per date are located and entered. What I'd like to do as expressed earlier, is click on the cell B6 or other related cells and instead of trying to figure out who employee #4 or #2 is, is to just click on the cell and have a photo of the employee appear as a macro. The ID numbering system is needed for my lookup functionality for wages, etc. I appreciate all your help!

**Leith Ross** · 07-20-2007, 04:17 PM

Hello Lilsnoop,

Where did you want the pictures to pop up? B6 through G34? Also, do you have any naming convention for the picture files file names, or are you going to name them by just the ID number?

Thanks,
Leith Ross

**lilsnoop** · 07-20-2007, 05:45 PM

The pictures will be identified by their ID# (1 through 34) and reference where the picture will pop up question.. I was hoping that if I were to click on lets say cell B6 which has the ID#4 in it, a comment type window would pop up with the photograph assigned the ID#4. If the cells B7 thru B34 are empty nothing will pop open if there is no data for a photo to correspond to and the same for column G (G6 thru G34). I was also hoping that if I were to click out of that cell the pop up window with that picture display would dissappear, etc and only reappear if that particular cell was clicked on again... Currently I don't have an area for the photo to pop open in any other area other than a comment type window. Let me know if I'm not making sense.. and thanks again for your assistance in this matter. It is greatly appreciated!

My folderpath for my pictures will be ("C:\sicktimepics\")
picture titles will be as an example 4.jpg

**Leith Ross** · 07-22-2007, 08:20 PM

Hello Lilsnoop,

After thinking over the pros and cons of having the pictures saved as part of the worksheet (using comments to store the pictures), or displaying them on demand (when a cell is clicked), your idea of displaying them on demand in a UserForm won.

This gives the program more flexibility, makes it easier to maintain, and reduces the program's size. The downside is the advanced coding needed to achieve the goal. Lucky for you, you have some one to code it for you. The real workhorse is a module I wrote using API calls to make a Form behave, and look like a regular Window. The Form has these features: Close button, Minimize button, Restore button, Icon, Resizable by dragging, and Auto sizes to match the Picture.

To have the macro work on any sheet, other than the "Lookup" sheet, required Sub-Classing Excel's application events. The easy way would have been to simply copy the macro to each Worksheet's Change event procedure, but not very practical, especially with 28 worksheets involved.

The attached workbook has all the macro code added, and has been tested. Unfortunately for those of you interested in the code, it is too long to list here. You will have to download the Workbook.

Sincerely,
Leith Ross

**lilsnoop** · 07-22-2007, 08:59 PM

Leith, Thank you so very much!! It works great!

**lilsnoop** · 07-23-2007, 07:01 PM

Hey Leith, I've noticed that if I leave my workbook open for about 15 to twenty minutes without working on anything and then try to click on an ID# in either column B or G, I get a runtime error '75', which states path/file access error. I then checked my visual basic window and tried to access my UserForm1, which also stated "Path/File access error". I tried this on two separate computers running excel 2007 and received the same error messages. I believe it has something to do with the autosave functionality of excel. Any ideas how I might be able to get this to work even after autosaves? It works great other than this slight issue. If I close out and re-open it will work good again until sitting dormant pass the autosave time. I appreciate your help!

**Leith Ross** · 07-23-2007, 07:56 PM

Hello Lilsnoop,

Sorry, I don't 2007 on my computer. Maybe some one else here at the Forum can answer that for you. If not, you may want to post your question in other news group, like Microsoft.Public.Excel.Programming.

Sincerely,
Leith Ross