Lookup Images with VBA

This approach is a bit different from McGimpsey, but it works. See attached workbook - which only contains six images, which themselves were all sized to exactly the same dimensions to aid consistency in display.

Each of the shaded cells used a Data Validation list to present a numeric selection range.

You will need to copy and paste all images into the worksheet. I've used the Excel default name of Picture X (where X = a number).

The code joins this fixed string "Picture " to the number selected in the cell to determine which picture to display next to the selection.

Please Login or Register  to view this content.

HTH,

Palmetto

Palmetto, thanks for the quick help. I suspect that for me to change the entries in my validation list to user friendly names like "Fallbrook", I need to name the picture "Picture Fallbrook"...?

I suspect that for me to change the entries in my validation list to user friendly names like "Fallbrook", I need to name the picture "Picture Fallbrook"...?

Well, to make it work with the code as given, you could just create a look up table.

Column-1 of the table = "Friendly Name" and is the list of items in the drop down.
Column-2 of the table = sequential numbers
Column-3 (optional) - could be a cross-reference between the "friendly name" and the default Excel name of Picture X.

This approach requires that you use a look up formula to match the friendly name to its associated number.

The code would have to be adjusted to reference the results of the look up cell.

When you add the pictures to the worksheet, Excel uses a default name of Picture X ( as I pointed out earlier). I would opt to minimize the work and simply use the default name because it will work with code as given.

You can rename a picture by selecting it and typing a new name for in in the name box but this will mean major revision to the code.

See attached - revised per above approach.

Ok, so I have all of the pictures tied to the code and my validation list. I ran into a couple issues though. When I would select a new door, if there was an existing door...the existing door may have an "Order" that places it on top, leaving my newly selected door behind... So I added a like of code to place the newly selected door "On Top".

While this helps...I still have an issue. Since there are always doors showing, If I select a door as an alternate that is already used in a different alternate...It'll move the image, but the previous alternate will still show the name of the door, but show the door that was below. I attached the spreadsheet so you can see what I mean.

The doors almost need to be invisible unless they are explicitly selected, and get positioned where they are selected.... Not sure how to do that. Hope you have a thought on this.

Oh, I also changed the positoning coordinates for Left & Top to serve my layout.

Try this amended code: Once a selection is made, it will check to see if there are any cells that already have that selection and, if so, set them to "No Selection", which removes the duplicate text and makes the default picture appear.

Please Login or Register  to view this content.

First...thank you for being so patient. I dropped the code in and tried to pick the same door for two selections to test the new changes. There appears to be some sort of circular reference...the image literally moves back and forth between the two selections until I hit escape to bring up the debugger.

OK. This is tested and works as needed. Give a try.

Please Login or Register  to view this content.

What is the relationship with "Image#.Picture" and the image. I'm asking because I would need to make about 6 descrete selections...

Nevermind...I got it. It's working correctly with the additional image control added. Is there any way to have this work and have the images contained IN the actual spreadsheet as apposed to in the same folder that the spreadsheet resides in?

Is there any way to have this work and have the images contained IN the actual spreadsheet as apposed to in the same folder that the spreadsheet resides in?

Seems you haven't tried my amended code/workbook, which works with the images in the sheet and now resets the image to "No Selection" when a duplicate selection is made in another drop down.

If the images need to reside outside the spreadsheet, is possible to have it point to a shared directory or does it have to be in the same folder as the spreadsheet. I'm asking, because there are several people who use this quotation sheet.

So they can exist in another folder, but not in the actual spreadsheet....right? So if I wanted to email it to someone...I'd have to send the folder (like you did for me)..? If so, I think I can live with that...actually gives a little control over the spreadsheet once it leaves the facility.

So yes, the new directory would be on our server...I just dont know how to map that in the code. \\ServerName\folder.....???

The path for where I'd like it to be is:

\\Pcsfile01\Design\DESIGN & ENGINEERING\Order & Bid Masters\Door Panel Pictures\

OR

L:\DESIGN & ENGINEERING\Order & Bid Masters\Door Panel Pictures

Ok...it worked. Thanks a ton. And just so I understand...this approach cannot be executed by refering to them within the spreadsheet. Said another way...is there a way to have the images saved in the spreadsheet (self contained..portable and ready to go), or does it have to refer to an outside directory?

If you think it's worthwhile...trust me, I'm super gratefull for having got to this point. The behavior of the spreadsheet is working flawlessly...just how I wanted. Having the images outside the spreadsheet just makes me suspect that I may be getting phone calls later from people outside our network.

Absolutely perfect!!! The boss is VERY happy... To both Leith Ross and Palmetto, thank you for taking the time work this out with me. My turn to help someone out with some non-vba stuff. Thanks again gentlemen.

Glad you got the solution you needed.

Please be sure to mark your thread as solved and don't forget to click on the "scales" icon to add to the reputation of those who helped.

Certainly!!!! How do I flag it as solved though?

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

To add to the reputation:

On a post to which someone has responded, click the "scale" icon. It is self-explanatory from there.

Validation lists are clean. When I debug, it's on the below line:

Case "$C$6"
Image1.Picture = PastePicture

Of course C6 is where I was making my selection just before the error came up. Also noteworthy... when I put my cursoor over "PastePicture" in the code...I get a tool tip message saying "PastePicture = Empty"

Ok Leith, I took the functioning spreadsheet you gave me and simply copied the information to another spreadsheet. I did this so you can see how it no longer works... If you can figure out how to fix this attachment...I suspect you can tell me how to fix the my quotation sheet.

Michael

Ok, so I managed to get hung up again. This attachment has the code, and has the module like you said, yet still has an error... I'm sorry for being such a pain...

Run-time error '424'

Object required.

Leith Ross...you're the man.... It's working now. I was getting a compiler error: Ambiguous Name.... So I checked my modules, I copied the PastePicture macro over more than once.. Thank you sooooo much for not throwing in the towel!!

Leith,

You helped me with a spreadsheet a while back and since then, haven't needed to use it much. It has come back to me and I am using Excel 2007 now...not sure if this is why the image control you helped me with doesnt work now.

I attached a simple example of what we last worked on. For some reason, the names of the images are not being matched or something. If you could help me out with this, I'd appreciate it!