Picture Help

[kingsolo]

Okay,I have a new problem dealing with pictures. I have tried to follow the instructions from the McGimpsey & Associates site (http://www.mcgimpsey.com/excel/lookuppics.html) to no avail. I just can't seem to get it all working proper. Basically, what I am trying to do, is make a drop down list show the picture above it. I have tried to change everything, and I am sure I am just a hair away from getting it right. I got the spreadsheet to where it will pull up the "Picture #" in the appropriate box, and even had it delete/hide all my pictures (I say delete/hide because I couldn't get them back after I removed the coding I was working on).


Another nice function I would like would be a button, around cell B74, to erase all the picks and reset the sheet for the next season... it would have to have a prompt to verify the user was absolutely sure they wanted to do that though! But I don't know where to begin on that button issue. Can someone point me in the right direction?

The spreadsheet is too big to attch, so it's hosted at the link below. The sheets involved are NFL Survivor & PicTable. On sheet PicTable, the Names & Pic #'s have already been named PicTable for ease of coding.


http://www.box.net/public/sm77z1dspy


Thanks in advance for the help.

Chris

[VBA Noob]

Hi,

This site might help

http://www.jkp-ads.com/Articles/ShowPicture00.htm

VBA Noob

[kingsolo]

Thanks for the reply... I'll give that one a shot. How about a reset button to reset drop lists? Any ideas where I'd look for that one?

[VBA Noob]

Hi,

try this site

http://www.contextures.com/excelfiles.html#DataVal

Download this file

http://www.contextures.com/DataValReset.zip

VBA Noob

[kingsolo]

VBA Noob... I'm getting an error on my sheet when I place this in it. It works fine in yours. Any ideas? It's reseting the first drop list to blank (It should be "Team") and giving the error.


"Line 14 -" Set rList = ActiveWorkbook.Names(Right(.Formula1, Len(.Formula1) - 1)).RefersToRange

[kingsolo]

Okay... I have figured out part of my problem. I have finally figured out how to get the sheet working as demonstrated at the McGimpsey site (http://www.mcgimpsey.com/excel/lookuppics.html). My problem is now, how do I get the second validation list to bring the pictures up in cell B2? Can it even be done?

[Bryan Hessey]

Okay... I have figured out part of my problem. I have finally figured out how to get the sheet working as demonstrated at the McGimpsey site (http://www.mcgimpsey.com/excel/lookuppics.html). My problem is now, how do I get the second validation list to bring the pictures up in cell B2? Can it even be done?

Hi,

the code that displays the picture is set for A1 only, try B1 as well

Please Login or Register  to view this content.

hth
---
are you intending this for multiple cells?

[kingsolo]

That works, but is there a way to limit it to that specific row only? For example, if I was to put another row of data validations on the sheet, and the bears were selected twice, only 1 instance of the bears picture can be on there. I guess I could do another validation list for each row and rename the picture, but there has to be another way that I'm not seeing. I'm attaching a new example to better explain my issue. It would be fine if it only affected the row, but since it affects the entire sheet, that's where my issue comes in.

[Bryan Hessey]

That works, but is there a way to limit it to that specific row only? For example, if I was to put another row of data validations on the sheet, and the bears were selected twice, only 1 instance of the bears picture can be on there. I guess I could do another validation list for each row and rename the picture, but there has to be another way that I'm not seeing. I'm attaching a new example to better explain my issue. It would be fine if it only affected the row, but since it affects the entire sheet, that's where my issue comes in.

Hi,

I have not been to the original site to see the definition, but the code
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
sets the picture to the top & left of the cell, so if you want to display the same picture twice you would need two copies.

That could get a little messy from what I remember of your original sheets.

hth
---

[kingsolo]

Yeah, that's what I was afraid of. At least my pictures aren't huge... my only draw back is if I do this and get 50+ people playing in that pool, I will have 1600ish pictures! Woah! I just did that math and it shocked the crap out of me! I need to re-evaluate this one! Thanks for your help nonetheless!

[Bryan Hessey]

Yeah, that's what I was afraid of. At least my pictures aren't huge... my only draw back is if I do this and get 50+ people playing in that pool, I will have 1600ish pictures! Woah! I just did that math and it shocked the crap out of me! I need to re-evaluate this one! Thanks for your help nonetheless!

nah, there must be a way, I remember an oldchippy post from many moons ago where a camera was involved, and the camera displays what is focused on.

I will try to find that, and if not get oldchippy to search his gray matter for the details.
---

[VBA Noob]

Bryan,

My original post shows the camera option

http://www.jkp-ads.com/Articles/ShowPicture00.htm

VBA Noob

[Bryan Hessey]

Bryan,

My original post shows the camera option

http://www.jkp-ads.com/Articles/ShowPicture00.htm

VBA Noob

it does too, (as I said, I hadn't been to the original site, which I should have)

The original shows pictures based on Named ranges

Picture =OFFSET(Sheet1!$C$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0,1,1)
and
PictureList =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

whilst the OP is using

PicTable =Sheet2!$A$1:$B$4
and some VB code, the code of which seems to determine that the picture is designated to a cell, which will mean that a separate pic will then be required for each display instance.

waiting to see what kingsolo decides.
---

[kingsolo]

So, if I am understnading, either way will result in me having an absorbant amount of pictures in the workbook, right? I was planning on using this technique (Validation to show pictures) in other sheets of this book, but it's looking like that's a no-go if that's the case. Am I following right? I tried the other link VBA Noob posted, and couldn't get the hang of it... of course, it took me a week to get the hang of the McGimpy one too! I just need to play with it if it only requires 1 instance of the picture.

Let me know.

[Bryan Hessey]

So, if I am understnading, either way will result in me having an absorbant amount of pictures in the workbook, right? I was planning on using this technique (Validation to show pictures) in other sheets of this book, but it's looking like that's a no-go if that's the case. Am I following right? I tried the other link VBA Noob posted, and couldn't get the hang of it... of course, it took me a week to get the hang of the McGimpy one too! I just need to play with it if it only requires 1 instance of the picture.

Let me know.

Hi,

from my reading (and testing) the method given by VBA Noob in his first post does not require more than one copy of a picture to have it displayed in more than one place. The code given at your original site appears to need a separate copy for each display.

hth
---
note, check the two Names were setup for the former, as per my previous post.
---
added
sample file - I'm not surprised you didn't get this working, it's as fickle as the English weather when the cricket is on at Lords. (sorry oldchippy)

Create (as per sheet3) the list of teams and their associated pictures

Create the PictureList name

Sheet1 - Data validate in column A - set to List =Picturelist

blank at column B to see the dropdown, otherwise the picture kills the arrow.

At C, Shift-CopyPicture - copy the first picture in your list and paste it in column C, whilst selected set formula
=Picture5

define a named range Picture5 - same as picture2, 3 & 4 but with $A$5 as the reference.

and if that didn't work then refer back to http://www.jkp-ads.com/Articles/ShowPicture00.htm and start again.

---

[kingsolo]

All-

Thanks for all your help trying to get this problem worked out. As it is, I have decided to scratch it. Bryan, it was super finicky! I got it working, but when I saved it and came back the next afternoon, it was broken. I haven't been able to get it back working in my workbook. Works fine in the test I did, but oh well. That's one of those items that I think is better left alone! I wanted to thank all of you guys for your help in this... I guess it's just excel's picture capabilities.

Chris

[Bryan Hessey]

All-

Thanks for all your help trying to get this problem worked out. As it is, I have decided to scratch it. Bryan, it was super finicky! I got it working, but when I saved it and came back the next afternoon, it was broken. I haven't been able to get it back working in my workbook. Works fine in the test I did, but oh well. That's one of those items that I think is better left alone! I wanted to thank all of you guys for your help in this... I guess it's just excel's picture capabilities.

Chris

hris,

strange that it would break after it was done, the one I did (with the instructions given) works fine, as can be seen in the Pics3.xls, but it is very specific in the setup, and you do need to carefully control the Picture1 names used or you will break it.
PictureC1 for cell C1 is a good idea in naming.

http://www.parry.co.nz/wc.zip shows what you can do with pictures.

---