help with picture links in dashboard style application

**jbaich** · 06-02-2017, 04:24 PM

Hi all, I have been learning more and more VBA over the last couple years, but still pretty novice and really really novice when it comes to dealing with images, shapes and the like.

I have attached a workbook containing a simplified example of what I've got so far and a bunch of comments in the sheets to explain what I'm doing and some specifics relating to the questions I have, which are:

1. If you open the attached workbook and see what I'm trying to accomplish; is this the best way? would you take a different approach?

Fyi, I've only included images for the first 3 products to demonstrate the approach I'm currently on...

2. Is there a way to modify hyperlink properties to populate a cell (currently using validation list) which is the lookup criteria for my dashboard images? Or an alternative 'on click' type of method or event? If so would it be one or the other, or could they (validation list and hyperlink) work in conjunction with each other? The existing "searchable" validation list works, but it seems a little rough around the edges and I know the end users will want to be able to click on a row in the table below to select the images displayed in the upper pane.

3. The data for the pictures exists in hundreds of individual workbooks. Can I use a macro to populate the Photo List sheet? I'm thinking if possible; to copy the ranges as an array of 3 images (or shapes?), find the appropriate row in the Photo List sheet (index and match?) and paste them as picture links in the correct cells? If it has to be done manually by copying and pasting one image at a time, I don't think it will be worth it.

4. Having no real experience using picture links... would having active links to hundreds of external workbooks make this super slow? If so, can I manually update links like i can for sheet calculations? maybe do it like once a day or something?

5. If this is a shared workbook with multiple users potentially updating/adding to it, does that change what can be done regarding the questions above? I know that some of the formatting options with shared workbooks are unavailable, but not sure if there are any work arounds?

Any advice or guidance would be very much appreciated!
Thanks,
Joe