Insert Picture from dropdown selection

[Mike at Channel]

I'm working at a Marina and trying to make a comparison chart for different
types of boats. I am trying to make a list, and based upon the users
selection of a boat, insert a corresponding picture to match the boat
selected from that list. I've researched other thread discussions, and tried
to approach it this way, as reccommended by Ron Coderre:

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture, put it in the sheet and
resize it).
1b. Select the range of cells that contains the picture.
1c. Name that range of cells, using the prefix "pic" followed by the
dropdown list text:
Example for a picture of an Elephant on cells A2:D10:
Select those cells
Insert>Name>Define
Name: picElephant

2)Build your data validation list on a cell in Sheet1 and pick one of the
items.
If you need help: use Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html

3)Create a dynamic range name that refers to that cell:
Insert>Name>Define
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
....or whatever cell you chose.

4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.

5)With picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic


After trying this method, instead of getting the picture inserted, I get the
name of the cells that the picture is in...(the name I gave the cells that
contained the picture). It does change according to my selection from the
list, but doesnt display the picture. The text that is returned instead of
the picture seems distorted as well. Any ideas of what I could be doing
wrong? I've also tried making this work through macros which I am even
less familiar with. I was able to get that to work for one selection, but I
dont know how to properly code it to work for every boat in my list. I used
this code reccommended by Bernie Deitrick:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
Range("B9").Select
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\PHIL\My Documents\My
Pictures\test.jpg").Select
Selection.Name = "PictureName"
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Resume Next
ActiveSheet.Shapes("PictureName").Delete
End If
End Sub

I assume I would need to somehow nest this or create If - Else commands?
Also to clarify, I did change the file names and paths to those that matched
my project. It worked for one selection, but I don't know how to code it for
a list of say 40 boats

Any suggestions of the easiest way to go about doing this? Ive been trying
for a few days now, and could use any help I can get!

[Ron Coderre]

I'll try to address the method I posted.

With a value in the drop down list, you should be able to:
Edit>Go to>
In the reference field, type: ShowMyPic
Click the [OK] button

That should bring you to the range that contains the picture that is
referenced.
If it does not, then the problem is in your definition of ShowMyPic

Example:
with this definition...
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)

If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel,
then ShowMyPic will refer to that range.

In order to help, I'll need a little more information:
1)What is the exact location of your dropdown list (sheet name and cell
address)?
2)What is the exact name you gave to one of the pictures?


***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike at Channel" wrote:

> I'm working at a Marina and trying to make a comparison chart for different
> types of boats. I am trying to make a list, and based upon the users
> selection of a boat, insert a corresponding picture to match the boat
> selected from that list. I've researched other thread discussions, and tried
> to approach it this way, as reccommended by Ron Coderre:
>
> Select Sheet2 and turn off Grid Lines
> (Tools>Options>View tab:Uncheck Grid Lines)
> 1)For each picture to be displayed:
> 1a. Insert>Picture from file. (select picture, put it in the sheet and
> resize it).
> 1b. Select the range of cells that contains the picture.
> 1c. Name that range of cells, using the prefix "pic" followed by the
> dropdown list text:
> Example for a picture of an Elephant on cells A2:D10:
> Select those cells
> Insert>Name>Define
> Name: picElephant
>
> 2)Build your data validation list on a cell in Sheet1 and pick one of the
> items.
> If you need help: use Debra Dalgleish's site:
> http://www.contextures.com/xlDataVal01.html
>
> 3)Create a dynamic range name that refers to that cell:
> Insert>Name>Define
> Name: ShowMyPic
> RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
> ...or whatever cell you chose.
>
> 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.
>
> 5)With picture selected, type this in the formula bar, then press [Enter]:
> =ShowMyPic
>
>
> After trying this method, instead of getting the picture inserted, I get the
> name of the cells that the picture is in...(the name I gave the cells that
> contained the picture). It does change according to my selection from the
> list, but doesnt display the picture. The text that is returned instead of
> the picture seems distorted as well. Any ideas of what I could be doing
> wrong? I've also tried making this work through macros which I am even
> less familiar with. I was able to get that to work for one selection, but I
> dont know how to properly code it to work for every boat in my list. I used
> this code reccommended by Bernie Deitrick:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count <> 1 Then Exit Sub
> If Target.Address <> "$A$1" Then Exit Sub
> If Target.Value = "Yes" Then
> Application.ScreenUpdating = False
> Range("B9").Select
> ActiveSheet.Pictures.Insert( _
> "C:\Documents and Settings\PHIL\My Documents\My
> Pictures\test.jpg").Select
> Selection.Name = "PictureName"
> Range("A2").Select
> Application.ScreenUpdating = True
> Else
> On Error Resume Next
> ActiveSheet.Shapes("PictureName").Delete
> End If
> End Sub
>
> I assume I would need to somehow nest this or create If - Else commands?
> Also to clarify, I did change the file names and paths to those that matched
> my project. It worked for one selection, but I don't know how to code it for
> a list of say 40 boats
>
> Any suggestions of the easiest way to go about doing this? Ive been trying
> for a few days now, and could use any help I can get!
>

[Mike at Channel]

Ron,
Thanks for the response, Here's exactly what I've created as a trial
experiment:
3 worksheets: Sheet one contains a drop down list created using data
validation in cell A5. I want the picture that matches the selction from that
to be displayed in cell A2 of that same sheet, sheet 1. On Sheet two I have
three pictures of boats. Picture one is placed over cells A1-D6 (
=Sheet2!$A$1:$D$6) and is named "picOne". Picture two is placed over cells
A7- D12 (=Sheet2!$A$7:$D$12) and is named "picTwo". Picture three is placed
over cells A13- D18 (=Sheet2!$A$13:$D$18) and is named "picThree". Finally on
my third worksheet I have my list of names (One, Two, and Three) on cells
A1-A3 used for my data validation on sheet 1.

"Ron Coderre" wrote:

> I'll try to address the method I posted.
>
> With a value in the drop down list, you should be able to:
> Edit>Go to>
> In the reference field, type: ShowMyPic
> Click the [OK] button
>
> That should bring you to the range that contains the picture that is
> referenced.
> If it does not, then the problem is in your definition of ShowMyPic
>
> Example:
> with this definition...
> Name: ShowMyPic
> RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
>
> If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel,
> then ShowMyPic will refer to that range.
>
> In order to help, I'll need a little more information:
> 1)What is the exact location of your dropdown list (sheet name and cell
> address)?
> 2)What is the exact name you gave to one of the pictures?
>
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Mike at Channel" wrote:
>
> > I'm working at a Marina and trying to make a comparison chart for different
> > types of boats. I am trying to make a list, and based upon the users
> > selection of a boat, insert a corresponding picture to match the boat
> > selected from that list. I've researched other thread discussions, and tried
> > to approach it this way, as reccommended by Ron Coderre:
> >
> > Select Sheet2 and turn off Grid Lines
> > (Tools>Options>View tab:Uncheck Grid Lines)
> > 1)For each picture to be displayed:
> > 1a. Insert>Picture from file. (select picture, put it in the sheet and
> > resize it).
> > 1b. Select the range of cells that contains the picture.
> > 1c. Name that range of cells, using the prefix "pic" followed by the
> > dropdown list text:
> > Example for a picture of an Elephant on cells A2:D10:
> > Select those cells
> > Insert>Name>Define
> > Name: picElephant
> >
> > 2)Build your data validation list on a cell in Sheet1 and pick one of the
> > items.
> > If you need help: use Debra Dalgleish's site:
> > http://www.contextures.com/xlDataVal01.html
> >
> > 3)Create a dynamic range name that refers to that cell:
> > Insert>Name>Define
> > Name: ShowMyPic
> > RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
> > ...or whatever cell you chose.
> >
> > 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.
> >
> > 5)With picture selected, type this in the formula bar, then press [Enter]:
> > =ShowMyPic
> >
> >
> > After trying this method, instead of getting the picture inserted, I get the
> > name of the cells that the picture is in...(the name I gave the cells that
> > contained the picture). It does change according to my selection from the
> > list, but doesnt display the picture. The text that is returned instead of
> > the picture seems distorted as well. Any ideas of what I could be doing
> > wrong? I've also tried making this work through macros which I am even
> > less familiar with. I was able to get that to work for one selection, but I
> > dont know how to properly code it to work for every boat in my list. I used
> > this code reccommended by Bernie Deitrick:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Cells.Count <> 1 Then Exit Sub
> > If Target.Address <> "$A$1" Then Exit Sub
> > If Target.Value = "Yes" Then
> > Application.ScreenUpdating = False
> > Range("B9").Select
> > ActiveSheet.Pictures.Insert( _
> > "C:\Documents and Settings\PHIL\My Documents\My
> > Pictures\test.jpg").Select
> > Selection.Name = "PictureName"
> > Range("A2").Select
> > Application.ScreenUpdating = True
> > Else
> > On Error Resume Next
> > ActiveSheet.Shapes("PictureName").Delete
> > End If
> > End Sub
> >
> > I assume I would need to somehow nest this or create If - Else commands?
> > Also to clarify, I did change the file names and paths to those that matched
> > my project. It worked for one selection, but I don't know how to code it for
> > a list of say 40 boats
> >
> > Any suggestions of the easiest way to go about doing this? Ive been trying
> > for a few days now, and could use any help I can get!
> >

[Cutter]

Have you had a look at JE McGimpsey's site:

http://www.mcgimpsey.com/excel/lookuppics.html

I think it is exactly what you're trying to do.

[Ron Coderre]

You don't mention if the model you created works. Does it?

If not...did you create the dynamic ShowMyPic range?


***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike at Channel" wrote:

> Ron,
> Thanks for the response, Here's exactly what I've created as a trial
> experiment:
> 3 worksheets: Sheet one contains a drop down list created using data
> validation in cell A5. I want the picture that matches the selction from that
> to be displayed in cell A2 of that same sheet, sheet 1. On Sheet two I have
> three pictures of boats. Picture one is placed over cells A1-D6 (
> =Sheet2!$A$1:$D$6) and is named "picOne". Picture two is placed over cells
> A7- D12 (=Sheet2!$A$7:$D$12) and is named "picTwo". Picture three is placed
> over cells A13- D18 (=Sheet2!$A$13:$D$18) and is named "picThree". Finally on
> my third worksheet I have my list of names (One, Two, and Three) on cells
> A1-A3 used for my data validation on sheet 1.
>
> "Ron Coderre" wrote:
>
> > I'll try to address the method I posted.
> >
> > With a value in the drop down list, you should be able to:
> > Edit>Go to>
> > In the reference field, type: ShowMyPic
> > Click the [OK] button
> >
> > That should bring you to the range that contains the picture that is
> > referenced.
> > If it does not, then the problem is in your definition of ShowMyPic
> >
> > Example:
> > with this definition...
> > Name: ShowMyPic
> > RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
> >
> > If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel,
> > then ShowMyPic will refer to that range.
> >
> > In order to help, I'll need a little more information:
> > 1)What is the exact location of your dropdown list (sheet name and cell
> > address)?
> > 2)What is the exact name you gave to one of the pictures?
> >
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Mike at Channel" wrote:
> >
> > > I'm working at a Marina and trying to make a comparison chart for different
> > > types of boats. I am trying to make a list, and based upon the users
> > > selection of a boat, insert a corresponding picture to match the boat
> > > selected from that list. I've researched other thread discussions, and tried
> > > to approach it this way, as reccommended by Ron Coderre:
> > >
> > > Select Sheet2 and turn off Grid Lines
> > > (Tools>Options>View tab:Uncheck Grid Lines)
> > > 1)For each picture to be displayed:
> > > 1a. Insert>Picture from file. (select picture, put it in the sheet and
> > > resize it).
> > > 1b. Select the range of cells that contains the picture.
> > > 1c. Name that range of cells, using the prefix "pic" followed by the
> > > dropdown list text:
> > > Example for a picture of an Elephant on cells A2:D10:
> > > Select those cells
> > > Insert>Name>Define
> > > Name: picElephant
> > >
> > > 2)Build your data validation list on a cell in Sheet1 and pick one of the
> > > items.
> > > If you need help: use Debra Dalgleish's site:
> > > http://www.contextures.com/xlDataVal01.html
> > >
> > > 3)Create a dynamic range name that refers to that cell:
> > > Insert>Name>Define
> > > Name: ShowMyPic
> > > RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
> > > ...or whatever cell you chose.
> > >
> > > 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.
> > >
> > > 5)With picture selected, type this in the formula bar, then press [Enter]:
> > > =ShowMyPic
> > >
> > >
> > > After trying this method, instead of getting the picture inserted, I get the
> > > name of the cells that the picture is in...(the name I gave the cells that
> > > contained the picture). It does change according to my selection from the
> > > list, but doesnt display the picture. The text that is returned instead of
> > > the picture seems distorted as well. Any ideas of what I could be doing
> > > wrong? I've also tried making this work through macros which I am even
> > > less familiar with. I was able to get that to work for one selection, but I
> > > dont know how to properly code it to work for every boat in my list. I used
> > > this code reccommended by Bernie Deitrick:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Cells.Count <> 1 Then Exit Sub
> > > If Target.Address <> "$A$1" Then Exit Sub
> > > If Target.Value = "Yes" Then
> > > Application.ScreenUpdating = False
> > > Range("B9").Select
> > > ActiveSheet.Pictures.Insert( _
> > > "C:\Documents and Settings\PHIL\My Documents\My
> > > Pictures\test.jpg").Select
> > > Selection.Name = "PictureName"
> > > Range("A2").Select
> > > Application.ScreenUpdating = True
> > > Else
> > > On Error Resume Next
> > > ActiveSheet.Shapes("PictureName").Delete
> > > End If
> > > End Sub
> > >
> > > I assume I would need to somehow nest this or create If - Else commands?
> > > Also to clarify, I did change the file names and paths to those that matched
> > > my project. It worked for one selection, but I don't know how to code it for
> > > a list of say 40 boats
> > >
> > > Any suggestions of the easiest way to go about doing this? Ive been trying
> > > for a few days now, and could use any help I can get!
> > >

[Mike at Channel]

Cutter,
THANK YOU! I've been trying different methods for over a week now, and
finally got it to work. Ron, thanks as well for the response, I must have
been making some small error that I didn't recognize, a wrong reference or
something, but since the V lookup method worked for me, I will just go with
that. That is exactly how I wanted it to work. I appreciate all your help,
and prompt reply, and I think the message board is a great resource. Thanks
for helping out!

"Cutter" wrote:

>
> Have you had a look at JE McGimsey's site:
>
> http://www.mcgimpsey.com/excel/lookuppics.html
>
> I think it is exactly what you're trying to do.
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=513797
>
>

[Mike at Channel]

One More problem,
that solution worked great for one pull down list, but I'm trying to compare
two boats by selection, so I need apply this code to two dropdown lists:

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B12")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

This code works for my output in cell B12 from the dropdown list in B11. Now
I would like to do the same for my second dropdown list in C11, and would
like to display it in C12. Both dropdown lists are coming from the same list
named picTable on Sheet 2 as informed by your excel help. I can't seem to
just duplicate the code and change the range on the second code. I'm guessing
I have to nest the two codes into one code for that sheet? I don't know how
to properly code this. Any help?

Thanks again,
Mike

"Cutter" wrote:

>
> Have you had a look at JE McGimsey's site:
>
> http://www.mcgimpsey.com/excel/lookuppics.html
>
> I think it is exactly what you're trying to do.
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=513797
>
>

[Cutter]

Try this:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B12")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("C12")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Make sure you change the formula in C12 to refer to the drop down list in C11

And you'll have to do something to your lists so that the name for the picture already showing (from one list selection) does not show up in the other. This is because each picture can only be shown once. I think you'll have to have 2 lists.

[Mike at Channel]

Wonderful. Thank you Cutter, your codes work perfectly. This is exactly how I
wanted it to work. I did make two seperate lists and associated two different
pictures, and now I can select two of the same boats without losing the
picture(even though the point is to compare two different boats). I
definitely need some coding practice, but having someone to point you in the
right direction helps. I would have been lost without the help. Thanks.

Thanks to Ron as well for your approach. My minimal experience with Macros
made the approach a bit more complicated. It kept returning the text name of
the picture instead of the picture itself. I'm sure it had to do with one of
my definitions, or a small error with my code. But thanks for your input as
well.

Mike
"Cutter" wrote:

>
> Try this:
>
> Private Sub Worksheet_Calculate()
> Dim oPic As Picture
> Me.Pictures.Visible = False
> With Range("B12")
> For Each oPic In Me.Pictures
> If oPic.Name = .Text Then
> oPic.Visible = True
> oPic.Top = .Top
> oPic.Left = .Left
> Exit For
> End If
> Next oPic
> End With
> With Range("C12")
> For Each oPic In Me.Pictures
> If oPic.Name = .Text Then
> oPic.Visible = True
> oPic.Top = .Top
> oPic.Left = .Left
> Exit For
> End If
> Next oPic
> End With
> End Sub
>
> Make sure you change the formula in C12 to refer to the drop down list
> in C11
>
> And you'll have to do something to your lists so that the name for the
> picture already showing (from one list selection) does not show up in
> the other. This is because each picture can only be shown once. I
> think you'll have to have 2 lists.
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=513797
>
>

[Cutter]

You're very welcome. I'm a VBA novice myself so the real thanks for what I was able to give you goes to JE McGimpsey for writing the original code and providing it to people like you and me in a way that we can understand it and get it working for our specific needs.

PS - Do I get a great deal on a boat? ;-)

PPS - It would have to be an icebreaker if I do. Canada, eh.

[Jufa]

Hello,

I tried the method on the McGimpsey site, but it doesn't seem to work in my case. Well, it does exactly what it is supposed to do I guess, but I have other graphics on my sheet (another picture=a logo that should stay fixed and a bunch of Combo boxes). Isn't there a way to define a list of the photo's that should be hidden, rather then everything on the page? The problem is that the "Me.Pictures.Visible = False" command hides everything, including what should remain visible.
Help would be very much appreciated.

Kind regards,

[Cutter]

Hello Jufa

As I mentioned above, I'm not an expert in VBA but if you add a line to the code shown on the McGimpsey site you can retain your logo.

Try this:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
ActiveSheet.Shapes("Picture 8").Visible = True
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Notice the added line: ActiveSheet.Shapes("Picture 8").Visible = True
(You could add additional lines right after it for other pictures you want retained)

This will keep Picture 8 visible at all times so add that line to your code and use the name of your logo instead of Picture 8

[Jufa]

Hey Cutter,
Thanks for your input. That should safeguard my logo, but I'm not too sure about the dropdown boxes. They dissapear too.
However, I see other use for you line of code. it seems that if I use your line, but define all pictures that can be triggered as a result of my dropdown as ActiveSheet.Shapes("Picture 8").Visible = False and omit the Me.Pictures.Visible = False line, I might get where I want to be.

Don't have time to test it right now. What I'm trying to do is actually an "in between project". The basics are finished, but the fine-tuning is for when my main monthly recurrent projects are finished. I just wanted to say thanks right away though. I'll be in touch later to inform you whether it worked, or ask more questions

So thanks and see you later.

[Jufa]

Hello all,

I used cutter's code-line to hide all pictures I do not want to see (13 flags, picture 25 through 37). However, when I select an item now in the drop down list, I get a "Run-time error '13' Type mismatch. When I open the debugger, the arrow points to "For Each oPic In Me.Pictures" Can someone please have a look at my code and see what's wrong? I would LOVE to get this thing working. I left out the line "Me.Pictures.Visible = True" at the beginning as I see no use for it since I define all pictures NOT to show. Many thanks in advance!

Private Sub Worksheet_Calculate()
Dim oPic As Picture
ActiveSheet.Shapes("Picture 25").Visible = False
ActiveSheet.Shapes("Picture 26").Visible = False
ActiveSheet.Shapes("Picture 27").Visible = False
ActiveSheet.Shapes("Picture 28").Visible = False
ActiveSheet.Shapes("Picture 29").Visible = False
ActiveSheet.Shapes("Picture 30").Visible = False
ActiveSheet.Shapes("Picture 31").Visible = False
ActiveSheet.Shapes("Picture 32").Visible = False
ActiveSheet.Shapes("Picture 33").Visible = False
ActiveSheet.Shapes("Picture 34").Visible = False
ActiveSheet.Shapes("Picture 35").Visible = False
ActiveSheet.Shapes("Picture 36").Visible = False
ActiveSheet.Shapes("Picture 37").Visible = False
With Range("E3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

[Cutter]

Hi Jufa

To use this method you have to define the pictures that you want to show, not the ones that you want to hide. If you have too many then hopefully one of the VBA experts will jump in with an alternative method.

The line you took out is needed because the line that is now causing the error refers to it. And note that the line you took out is:
Me.Pictures.Visible = False (Not =True) as you stated in your last post

[Jufa]

Hey Cutter,

I'm now using exactly your code (except for the cell reference which I adjusted to my needs), but it keeps giving the error on the line "For Each oPic In Me.pictures". I have no clue of what I'm doing wrong. When I apply the same addition of code (ActiveSheet.Shapes("Picture 1").Visible = True) it works fine, it's just that in my sheet, the error keeps popping up.

Can the problem be, that it says "For Each oPic in Me.Pictures" while some pictures seem to be missing. For example. The pictures that are in my look-up table are pictures 25-37. The numbers before that refer to the logo and drop down boxes I guess. However, when I define those first 24, I'm told that he doesn't recognise some of them and I have to remove those numbers. I think this is due to the fact that while originally setting up my sheet I deleted some pictures/drop down boxes and added them again. Excel however, just keeps taking the next number instead of recycling the nrs of the pics that were deleted. Can this be causing my conflict and how can I solve this??

Kind regards,

[Cutter]

Let me see the code you're using. Keep in mind that I'm not an expert but I'll see if I can help.

You will definitely get an error if you tell Excel to make Picrure 5 visible if there is no Picture 5.

You can get the name of a picture by clicking on it and seeing what shows in the Name Box. Right click on a combo box for same result. (BTW - what kind of drop down lists are you using. I tried a combo box from the Forms toolbar and the original code did not hide it)

[Jufa]

Hey Cutter,
First of all, thanks for all your help. I was using a regular combobox like you mention, but maybe it's something in the settings (I use placement 1) that identifies them like pictures?
Anyways, I started over and used data validation now instead of the drop boxes and now it's working just fine. It looks a bit less professional in my opinion, but it will have to do. It has been distributed. I'm a financial analyst in the first place and I couldn't keep spending time in cosmetics. I think somehow something in my original sheet got messed up and the code SHOULD be working fine, but it just isn't. Maybe for a learning proces I'll post it. This is the most simple form, asking to keep the logo visible. I would have to add a few more to keep the drop boxes. Again, thanks for your efforts. Best of luck. I will still be checking out your comments though. I hate it when things don't work the way I want them too and I might distribute an update later It's the line "For Each oPic..." that seems to block things.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
ActiveSheet.Shapes("Picture 7").Visible = True
With Range("F3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

[Jono]

Hi Cutter is it possible to do this the other way around and have the
pictures themselves be in the drop down? I have many parts to choose from and
a person in the field probably won't know what the part number is but they
will be able to look at the part. It would be great if they were able to pick
it out from a list of pictures and then have the part number displayed in a
cell next to it.

Sorry to jump in like this but I haven't been able to get any response on
whether or not this is possible and whom to talk to. ANY help would be much
appreciated!

Thanks in advance.

"Cutter" wrote:

>
> Let me see the code you're using. Keep in mind that I'm not an expert
> but I'll see if I can help.
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=513797
>
>

[Cutter]

Jono

I doubt that it's possible. I've never heard of it being done (or even heard of anyone asking for it until now). But you'd have to hear from the VBA experts for a definite answer.

Cutter

[Jono]

Thank you, thank you , thank you! Finally an answer.
I'll try that group.

"Cutter" wrote:

>
> Jono
>
> I doubt that it's possible. I've never heard of it being done (or even
> heard of anyone asking for it until now). But you'd have to hear from
> the VBA experts for a definite answer.
>
> Cutter
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=513797
>
>

[CLR]

One thing you might consider, is to put the images in the comment boxes of
each cell with the description therein, the pictures pop up as you mouse
over the cell.......

hth
Vaya con Dios,
Chuck, CABGx3


"Jono" <[email protected]> wrote in message
news:[email protected]...
> Hi Cutter is it possible to do this the other way around and have the
> pictures themselves be in the drop down? I have many parts to choose from
and
> a person in the field probably won't know what the part number is but they
> will be able to look at the part. It would be great if they were able to
pick
> it out from a list of pictures and then have the part number displayed in
a
> cell next to it.
>
> Sorry to jump in like this but I haven't been able to get any response on
> whether or not this is possible and whom to talk to. ANY help would be
much
> appreciated!
>
> Thanks in advance.
>
> "Cutter" wrote:
>
> >
> > Let me see the code you're using. Keep in mind that I'm not an expert
> > but I'll see if I can help.
> >
> >
> > --
> > Cutter
> > ------------------------------------------------------------------------
> > Cutter's Profile:
http://www.excelforum.com/member.php...fo&userid=9848
> > View this thread:
http://www.excelforum.com/showthread...hreadid=513797
> >
> >