Insert Picture from dropdown selection

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!

0
Channel (1)
2/17/2006 7:31:16 PM
excel.newusers 15348 articles. 2 followers. Follow

21 Replies
1118 Views

Similar Articles

[PageSpeed] 26

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!
> 
0
2/17/2006 8:55:27 PM
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!
> > 
0
2/21/2006 9:39:27 PM
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?action=getinfo&userid=9848
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
2/21/2006 10:14:51 PM
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!
> > > 
0
2/21/2006 11:15:27 PM
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?action=getinfo&userid=9848
> View this thread: http://www.excelforum.com/showthread.php?threadid=513797
> 
> 
0
2/22/2006 1:40:21 PM
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?action=getinfo&userid=9848
> View this thread: http://www.excelforum.com/showthread.php?threadid=513797
> 
> 
0
2/22/2006 4:39:11 PM
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?action=getinfo&userid=9848
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
2/23/2006 1:52:07 AM
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?action=getinfo&userid=9848
> View this thread: http://www.excelforum.com/showthread.php?threadid=513797
> 
> 
0
2/23/2006 2:37:31 PM
You're very welcome.  I'm a VBA novice myself so the real thanks fo
what I was able to give you goes to JE McGimpsey for writing th
original code and providing it to people like you and me in a way tha
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

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=51379

0
2/23/2006 9:23:32 PM
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,


-- 
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30742
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/1/2006 9:40:05 AM
Hello Jufa

As I mentioned above, I'm not an expert in VBA but if you add a line t
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 yo
want retained)

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

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=51379

0
3/2/2006 2:34:13 PM
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
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30742
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/7/2006 2:14:06 PM
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


-- 
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30742
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/10/2006 4:01:16 PM
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


-- 
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9848
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/11/2006 10:23:52 PM
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,


-- 
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30742
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/13/2006 3:21:42 PM
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?action=getinfo&userid=9848
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/13/2006 4:37:32 PM
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


-- 
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30742
View this thread: http://www.excelforum.com/showthread.php?threadid=513797

0
3/15/2006 1:27:10 PM
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?action=getinfo&userid=9848
> View this thread: http://www.excelforum.com/showthread.php?threadid=513797
> 
> 
0
JonO (8)
3/21/2006 3:01:30 PM
Jono

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

Cutte

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=51379

0
3/21/2006 3:40:56 PM
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?action=getinfo&userid=9848
> View this thread: http://www.excelforum.com/showthread.php?threadid=513797
> 
> 
0
JonO (8)
3/21/2006 3:57:28 PM
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" <Jono@discussions.microsoft.com> wrote in message
news:912CC438-6A9B-4342-832A-7ACAD66C68F7@microsoft.com...
> 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?action=getinfo&userid=9848
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=513797
> >
> >


0
croberts (1377)
3/22/2006 1:14:51 AM
Reply:

Similar Artilces:

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

CFileDialog selecting directorys only
I Have a CFileDialog and I just want to be able to select directorys no files. Can this be done? use SHBrowseForFolder http://msdn.microsoft.com/library/default.asp?url=/library/en-us/shellcc/platform/shell/reference/functions/shbrowseforfolder.asp HTH, "Chris Baker" <ChrisBaker@discussions.microsoft.com> wrote in message news:5185346E-6097-4B9C-89B1-D6588C87249A@microsoft.com... >I Have a CFileDialog and I just want to be able to select directorys no >files. > > Can this be done? > You also might take a look at www.codeproject.com/dialog/cfolderdialo...

Averaging Selected Records
Hi, I have a continuous form with lots of data on it. In the form footer I would like to average certain fields by checking a checkbox next to the fields (in the detail section) I would like included in the average. I am having a brain fart on this and just not getting it to average the selected records. Can anyone point me in the right direction? Thanks "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:262B5AEB-2692-4561-824C-D6FC7465FC1D@microsoft.com... > Hi, > > I have a continuous form with lots of data on it. > > In the form ...

How can I print only select sheets in my workbook?
My workbook has 12 sheets, but I want to print the second through ninth sheet exclusively, each time I hit print. (I do this once a week.) How do I do it? Excel 2007 Select sheet2 then hold SHIFT key and click on sheet9 File>Print>Active Sheet(s) Gord Dibben MS Excel MVP On Thu, 27 Mar 2008 19:14:00 -0700, MVictoreen <MVictoreen@discussions.microsoft.com> wrote: >My workbook has 12 sheets, but I want to print the second through ninth sheet >exclusively, each time I hit print. (I do this once a week.) How do I do it? >Excel 2007 Hi MVictoreen! As an optional m...

Creating a dropdown list
How do I create a dropdown list in a cell within Excel? Debra Dalgleish's web page should have all the info you need to get started: http://www.contextures.com/xlDataVal01.html -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Len" <anonymous@discussions.microsoft.com> wrote in message news:00cb01c3fa59$1a70a890$a001280a@phx.gbl... How do I create a dropdown list in a cell within Excel? Try MVP Debra D's nice coverage at: http://www.conte...

how do I insert a filename into a formula from another cell?
I have a spreadsheet with a filename in a cell. I would like to reference that filename in a formula. I can't seem to get it to work. "+cell number" doesn't work. Any suggestions? You would normally use INDIRECT to do this, along the lines of: =INDIRECT("["&A1&"]Sheet1!C2") where A1 contains your filename (with the .xls extension) and you are trying to return data from C2 on Sheet1 of that file. However, INDIRECT will only work with files that are open, so you would have to have the file open for this to work. Hope this helps. Pete On...

picture special effects
How were the special effects on the pictures in sample brochures created. I was looking at the casual brochure section on a sample marketing cd that came from hewlett packard. The pictures are "waffled" splitting it up into sections. Would love to know how to create this effect. "Marcia" <Marcia@discussions.microsoft.com> wrote in message news:ECE5C2A7-99C2-4539-9EBB-AEB3530F5D8E@microsoft.com... > How were the special effects on the pictures in sample brochures created. > I was looking at the casual brochure section on a sample marketing cd > that ...

insert password using vb #2
Thank You Celtic Avenger I'll try that. Abbevill -- abbevill ----------------------------------------------------------------------- abbeville's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=976 View this thread: http://www.excelforum.com/showthread.php?threadid=26122 ...

Inserted Word Art will not appear
When inserting Word Art into my publication, the text does not appear and I cannot change the fill color or line color. The control points do appear, but not the actual text. There is nothing else currently on the page. Which version of Publisher are you using??? Are you using a standard True Type font? Do you mean when you create a WordArt object? Look under View, pictures, check detailed display. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Flo" <Flo@discussions.microsoft.com> wrote in message news:ABB082...

How do I save a picture from word into my pictures?
I have recently installed Office 2007. I am unable to copy and paste a picture from word 2003 onto publisher 2007. So I tried to save the picture into my pics and then insert but I can't do that either. Please help. Unique wrote: > I have recently installed Office 2007. I am unable to copy and paste > a picture from word 2003 onto publisher 2007. So I tried to save the > picture into my pics and then insert but I can't do that either. > Please help. ============================ Maybe the following links will help: (555171) WD: How To Extract Embedded Im...

Embedded pictures in email won't print
Pictures in email will not print, only a red X. Computer has Outlook 2003 SP1, W2k SP3. I have other computers where this is not an issue. I have made sure that all internets settings are set to default, but to no avail. Does anyone have a concrete solution? LF ...

New contacts not showing when selecting the "To" button on a new e
Hello, If I add a new contact in Outlook 2003. Then I go to compose a new email. When I select the "To" buttom and either search or look for the new contact I created. It doesn't not show up. Any help would be appreciated. Thanks, Tony Two possibilities: 1. Your Contact doesn't have a resolved, valid electronic address 2. You did not add the Contact to the same folder you are displaying in the address book view. -- Russ Valentine "Tony414" <Tony414@discussions.microsoft.com> wrote in message news:AE3B3E10-8FCD-4840-8924-5B33D99A1F25@mi...

inserting equations in Word document from MathType not working anymore
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I've just starting using Snow Leopard after worked with Tiger previously. I'm noticing some odd behavior that's different than before. <br><br>I have the MathType 6 Insert Eqn button installed in Office 2008 for Mac. When I click on that, MathType opens. So far, so good. I type in my equation, but, when I'm finished editing or creating my equation, I'm used to clicking on the red button to close the MathType window and return to my Word document. When I do that now, my Word d...

Copy Picture in 2007
Hi. Folks In Excel 03, I regulary use the (Shift/Edit Menu/Copy picture) to access the Copy Picture command. Please can you tell me how to access this in 2007. Your help is and always has been very much appreciated. Looking forward to your answer in anticipation. <-><-><-><-> Big Rick Big Rick - After making a selection, in Excel 2007, choose Home > Paste (drop down) > As Picture > Copy As Picture ... - Mike http://www.MikeMiddleton.com "Big Rick" <BigRick@discussions.microsoft.com> wrote in message news:1F58...

Catalog pictures in item list
Does anyone know of a way to display the pictures attached to the items when scrolling through the item list? I know you can click the picture button to pop up the picture, but is there a way to just have the pictures always there? I don't think there's any way out of the box. Someone may be able to write you an add-on to do that, but you would have to have a super fast processor and lots of RAM or it would probably feel like you were wading through quicksand. The overhead needed to accomplish it probably wouldn't be worth the benefit. Craig "Matt Bo" <MattBo@...

How to make Picture Manager license agreement disappear?
Every single time I open Picture Manager, the license agmt. pops up asking me to accept or decline. I've tried all options (accept, decline, print) on that screen, but it will still pop up every time the program is opened. Not a major problem, just annoying. If anyone has any ideas on how to make it stop popping up, I'd appreciate. BTW, my version of Word (2007) is good/registered version, so it should't have anything to do with registration, I don't think. Thanks lots! Sabrina ...

CFileDialog multiple file selection
I am using VC++ 6.0 I am trying to allow my users to select at most 50 files using CFileDialog When I over 18 files, the CFileDialog return IDCANCEL. I call CommDlgExtendedError() and it return #define FNERR_BUFFERTOOSMALL 0x300 I then check the first two bytes in lpstrFile and they are 24 and 1; which I take to mean 24*256 + 1 = 6145 The problem is, I have allocated a buffer of 20000 bytes. Can anyone tell me why this is failing I have included my code below Thanks Phi // Code Sampl TCHAR buf[20000] memset((void*)buf,0,20000) CFileDialog fldlg(TRUE,"All Files (*.*)|...

Selecting Maximum Values in a Query with joins to other tables
I'm having difficulty with a query and hope someone can help me out. Basically, I'm looking to select the maximum amounts from a table based on a column in one of the tables named StateID. It works fine when I do this: SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount) AS Amount, dbo.States.StateName FROM dbo.Bids INNER JOIN dbo.States ON dbo.Bids.StateID = dbo.States.StateID GROUP BY dbo.Bids.StateID, dbo.States.StateName ORDER BY dbo.States.StateName However, when I start to join fields from other tables in...

Outlook 2003 does not export all fields to any of the selected file formats
Outlook 2003 does not export all fields, it is missing the date and time field for messages. If there is a way please let me know. For what purpose are you exporting your .pst items? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Great Eyes asked: | Outlook 2003 does not export all fields, it is missing the date and | time field for messages. | If there is a way please let me know. "...

Connect a number to a picture bank and import that picture to exce
I have a colum with numbers for specific products (example 111,123456) and I want a picture in the colum to the right of it that matches the number. On the computer I have a folder with pictures named with the product numbers. So my question is: Is there a function to link the the number in for example cell A1 with the picture that has the same name as the value in A1 and put that picture in cell B1. Thanks to whom ever have an answer, it would help my life. Best Regards Dennis Hi Dennis Have a look at this site : http://www.mcgimpsey.com/excel/lookuppics.html HTH John...

How to fade background picture
I want to take a tree, fade it to aVERY light tint, and be able to type over it. Also--when I insert tree picture, it places 6 of them--two rows of three from top to bottom--on the card. Only want one--and want in centered and VERY light. Don't use the "background" format. Insert your tree, select it, click the color tool (looks like a bar chart) select wash out. You can place this on the Master Page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Connie D" <Connie D@discussions.microsoft.com> wr...

Selective enabling of add ons
It would be nice if IE would allow me to enable add-ons for certain web pages. For example, I need flash installed to access my Bank. But I get annoyed by all the flash-based ads on other websites. I would like to enable flash for my Bank & youtube only. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and th...

CImage, CStatic (Picture Control), CObArray problem
Please help! I need to create a dialog where the user can add, remove and navigate among images - one image shown at a time. So, I created the dialog, added the buttons for adding and so on, and a CStatic picture control to display one image. The dialog has got tabs, where the image handling is on one of the tabs. The pictures are stored in a CObArray as CImages. However, absolutely nothing works with these images... I've tried just about every tip I've found in this group, at MSDN, GodeGuru and everywhere. It might be because I'm not that experienced and clever VC-programmer. So ...

Cannot insert logo
When I design an invoice, I can put a logo in. When I save it, then reopen it, it still has the logo. When I go into money and print preview and invoice, the logo will not show up. Go back to the design, and the logo is there. Any color or font changes, or moving fields around are correct, just not the logo. ...

listview insert question
I am really having a time determining what is wrong with the inserting from a dropdownlist inside a listview. Everything else is working regarding the listview, except the insert. But, there must partially working as there is a new row added to the table, just not with information. When I add SelectedValue='<%# Bind("ApplicationItemID") %>' to the droplist I get an error. The error is as follows; "Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control." I have an insertcommand a...