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
1027 Views

Similar Articles

[PageSpeed] 34

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 text for Emailing
Hello, I have question that maybe someone could help me with. I have a database where on the form I placed a command button to send emails. Works great and in fact, I even got it to pull the information from selceted fields on the current record. What I would like to know is: Is there a way to pick up the name of the field and have the information that was inputted in that field appear next to the name in the email? For Example: Currently my email body picks up only the information so the loan number field only shows the loan number and the Date In field only show a date. 123456789 1...

Insert "name" and "auto ID" into tables by using a form
I have a table: ID Name Nationality 101 Allen Taiwan 102 Sarah USA 103 Steve Japan ------------------------------------------------------------------------------ I'd like to create a form with the following objects -- 2 textboxes (putting name and Nationality) -- a button (when i press it, it will insert values in the 2 textboxes into my database) ------------------------------------------------------------------------------ Big thanks in advance , experts ! -- Allen Phailat Wongakanit Actually, you don't need any code at a...

Which card format for picture on front and writing inside
Using Publisher 2003, Wich blank publication would you use to put a picture on the front of the card and text inside? Thanks Jim wood to art wrote: > Using Publisher 2003, Wich blank publication would you > use to put a picture on the front of the card and text > inside? > > Thanks Jim ========================== For a Blank Quarter Fold Card...choose Blank Publications / Side Fold Card. If you require a Half Fold Card...open a Side Fold Card and then go to... File / Page Setup / Booklet / OK. -- John Inzer return e-mail disabled ...

CTreeCtrl / deleting selected item
All I have a CTreeCtrl with theTVS_SHOWSELALWAYS style. If the program deletes the item that is currently selected, I would like to disable the program from automatically selecting a different item (I would like there to be no selection at all in this case). Anyone any idea how this could be achieved ? Many thanks in advance! Sorry - already figured it out. before running ... treeCtrl.DeleteItem(itemh); I am just calling this ... if (treeCtrl.GetSelectedItem() == itemh) treeCtrl.SelectItem(NULL); ...

Selective increase of numbers when pasting
Hi, if i have a formula with multiple cell numbers (ex: IF(A1=B1, A1) ) an i try to copy this formula to multiple cells by dragging from that cel down, it automatically adds +1 to each number. so the next ones would read IF(A2=B2, A2), IF (A3=B3, A3), and so on. How do i make it so only some of the numbers are increased? if i wan each A cell to compare to B1, i don't want B1 increased, but i want th A cells increased. my actual formula is a lot longer, i just picked a simple formula as a example, but same idea. Thanks -- Message posted from http://www.ExcelForum.com Take a look at &...

print selected from print preview?
I have a small application in Access 2000 that generally hides Access from the user. They can import data and print it in the required format. I've just added the ability for the user to see the report before it's printed simply by adding a button that opens the report in print preview mode, and added a print button to my custom toolbar. Problem is, clicking the print button defaults to printing ALL the pages - there isn't even a prompt! How can I give the user the Print Dialog window from a report's Print Preview page? Access and VB aren't what I normally do, so if yo...

inserting cell data into a hyperlink
On Sheet 2 of my workbook, there is a cell for the user to enter thei Zip Code. This Zip Code is needed for part of a hyperlink on Sheet 1. For example, if the Zip Code entered was 90210, the hyperlink would b www.internet.com/90210 . Is there a way to mesh entered text and hyperlink like this? Thanks, Jaso -- Message posted from http://www.ExcelForum.com Hi Jason try something like =HYPERLINK(www.internet.com/ & 'sheet2'!A1) -- Regards Frank Kabel Frankfurt, Germany > On Sheet 2 of my workbook, there is a cell for the user to enter their > Zip Code. This Zip Code is...

sending/forwording pictures
I cannot send/forward pictures to a gmail account. That gmail account does receive pictures from other email poviders just not mine. Anyone have an idea as to what the problem is. I have Vista hp and use Live mail. WLM-2008/2009: Tools (ALT+T), Send, Mail HTML Settings and make sure the Send Pictures is checked. WLM-2011: ALT+F, Options, Send, Mail HTML Settings and make sure the Send Pictures is checked. -- Mike - http://TechHelp.Santovec.us "chilimac34" <chilimac34@discussions.microsoft.com> wrote in message news:EB5D4981-8AF9-4B76-8EB4-A6A58C06...

Seeing my contact's picture on emails
Hi there... If I have added a picture to a contact in Contacts: is there a possibility to see his-her picture when I receive or when I'm reading an email from this contact? TIA JL "JL" <JL@dontspamme.com> wrote in message news:7neem1ps4i1l60nlr6mjveur706641a1jo@4ax.com... > Hi there... > > If I have added a picture to a contact in Contacts: is there a > possibility to see his-her picture when I receive or when I'm reading > an email from this contact? > > TIA > > JL Not to sure if OL can do this but I achieve it by using a program called...

how can I save a publisher created logo in picture format?
I've created a logo in MS Publisher. How can I save the logo in picture format so that it can be inserted into other programs? GEL <GEL@discussions.microsoft.com> was very recently heard to utter: > I've created a logo in MS Publisher. How can I save the logo in > picture format so that it can be inserted into other programs? What version of Publisher are you using? -- Ed Bennett - MVP Microsoft Publisher "Ed Bennett" wrote: > GEL <GEL@discussions.microsoft.com> was very recently heard to utter: > > I've created a logo in MS Publ...

Selecting Members for Distribution List
Am running Outlook 2007. I have a Contacts folder and other smaller folders. I have a Group distribution List named NWH in a folder named NWH contacts that I set up in 2003. I can delete from this list but not add to it, as the select members function only looks in the Contacts folder, even though I am looking at contacts in the NWH contacts folder when I select the NWH list to edit. ??? Thank you for any help. Claudette Too little information to permit an answer. What is a "Group distribution list?" Outlook does not use that term. How and where did you create ...

Spell Check
I am using Windows XP Home. When I spell check an email prior to SEND in Outlook Express 6 the resultant action is to try and install Microsoft Picture It which is already installed on my system. Error 1706 (No valid source)is obtained when I cancel the option to install. If I allow Picture It to be installed it keeps asking for Disc 4 even though Disc 4 has been placed in the drive and this has to be cancelled. I can use the installed version of Picture It without problems. What is the fix to allow Spell Checker to work. This newsgroup is for support of Outlook 97, 98, 2000, 2002 &...

Inserting worksheet in Word
When I insert a worksheet into a Word document I can't view it all. If I pull the handles to reveal the rest of the worksheet I see it but it "snaps" back to a smaller size. There is lots of room within the margins of the Word document for this worksheet to be displayed on one page. Why does it insist on only displaying part of it in Word? Connie Martin ...

Selecting individual records for a report
I have a table with 200+ contacts. I want to generate letters to 7-8 of them, but there is no common criteria upon which I can run a query. I'd like to create the letters, then when the report is run be able to select which records from the table receive the letter. No idea how to go about this (other than exporting to excel, deleting non-receivers, and doing a Word mailmerge). Suggestions? If there truly is no relationship, you could create a new table of records to report with a single field that is the primary key of you contacts table. Add or delete record...

Select stmt using Like
I have 2 tables in an db. Contacts is one with field LastName. The other is Issues. The issues db has a free text field that stores lastname/lastname... the field is called Owner. The objective is for multiple owners in an issue, but not necessarily from the Contacts table. I am trying to write a select statement that will allow me to compare the Contacts.LastName with any Issues.Owner. Select * From Issues Where Issues.Owner LIKE *[Contacts.lastname]* (except I cannot use this format) Any ideas would be appreciated. -- dan You can use a non-equi-join query (though it&#...

How to find number of selected files in CFileDialog
hi friends, if any one can tell me how to find the no. of selected files in CFiledialog before traversing the whole list of filename through POSITION data structure. Regards ravi in short, no. But why not just traverse the list once keeping a count then traverse again and do whatever you need to with the selections. I presume you need the total in order to prime some other variable. BTW if you're worried about the overhead of traversing twice, forget it. In comparison to the time it took the user to click on the files its' miniscule. Even compared to the time it took for ...

Spurious data being inserted in field
Hello Periodically, some of the records in one table have one field updated to the following value: <!--<1586567094>--> It is the most bizarre thing I have ever seen. We have checked all our servers and stored procedures for any occurrence of this value in scripts or ASP pages but none exist. We're fearing SQL injection or similar but in our application this field is only updated from one place and it's tight against hacking. If it was a hack we would think the value might be something else, so we're presuming some sort of corruption or sim...

How do I type in a tab label and get that tab to be selected?
I want to put a list of parts that go into an assembly into a data base (excel or access) and to be able to easily retreve that list. The idea is to type in the number of the assembly and have that data appear on screen. Any suggestions would be helpful. You can set up you Parts Detail Table in say A1:L100 on Sheet1, then if you type a part number in A1 of sheet2, you could make the data in the second column of your data table appear in B1 on sheet2 by entering in B2 =IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$L$100,2,FALSE)) You might also want to put it in Access and d...

dropdown data for form fill-in
I've created a form and it includes selecting data from a dropdown box, and it populates my table. However, when I run a query, it won't pick up the record that has the data criteria from the "dropdown" list. Help. Just a guess, but it sounds to me like you may have one of those evil table lookups defined at the table or query level. If this is the case, the value being stored in the table may not be as it appears. Please see the 2nd Commandment of Access here: The Ten Commandments of Access http://www.mvps.org/access/tencommandments.htm If you see a combo...

Row select mode to highlight active row of active cell
Yet another simple convenience for folks with large spreadasheets. Whatever row and/or column you are in gets highlighted. Sure, you could select the row or column heading, but so often you are tooling around a large spreadsheet and it would be nice not to have to finger your way over to track across. ---------------- 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 suggestio...

How to use the system Select Folder Dialog?
Hi All, In my application, how can I allow the user to select a folder?? I mean how can I use the system Dialog (Select Folder). I know that CFileDialog is doing the same for files but I don't know who to do it for Folders. Thanks >In my application, how can I allow the user to select a folder?? I mean how >can I use the system Dialog (Select Folder). I think SHBrowseForFolder is what you're looking for. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq Addtionally to Dave's answer, you could find a sample program which using SHBrowseForFolder for browse directories in ...

Help with Rules: can't select Public Folders.
Does anyone know why it's sometimes possible, but not always, to use a rule to move or copy a message to a public folder? I don't seem to be able to select the public folder; I can select it, but it doesn't stick. The inconsistency is a bit weird. I can't even make a, 'from scratch' exact copy of a currently working rule that moves a message to a public folder. Weird! -- Dab Cut off: yourhead to respond do you have full permission on the folder? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for W...

Pictures
I don't know if I am able to change the shape of a square photo to oval in Publisher or Word. lharris@cmhinc.org wrote: > I don't know if I am able to change the shape of a square > photo to oval in Publisher or Word. ================================ What version of MSPublisher are you running? For info on Microsoft Word... Go to the following site: http://tinyurl.com/218p Wait for the page to load. (if the newsgroup list doesn't load, left click your Refresh button) Scroll down the list in the left pane to: "word" (you'll find several choices) BTW,...

Print Selected Record
I want to print a popup form. I used the command button wizzard to set up a button to print the form. How can I filter what's printed to be only the selected (active) record? -- Thanks. Sammie Access 2003 Although allowed, Forms are really not designed for printing. Why not just create a report to present the data for the current record. Then you can use the Where argument of the OpenReport method to have the report limited to the active record on the form? -- Dave Hargis, Microsoft Access MVP "Sammie" wrote: > I want to print a popup form. I used the command...

Windows XP will not open up to the meadow picture
I turn on my computer and it shows the Windows XP symbol. Then it goes into the screen that asks to either Safe Mode Last known good configuration starting windows in XX seconds. Then the seconds go to zero and the loop starts again. It has gone on for 20 minutes on its own. What do I do? I tried the solutions that Microsoft suggested and they did not work. Example go to safe mode and last known... Do I understand that it will not go to Safe Mode or will it just not got to last known good configuration? "Pistol pete" <Pistol pete@discussions.microsof...