Insert picture when cell changes

I have read a few posts that come close but maybe some expert can offer a
more definitive solution here. I have a drop down list in cell A6, it is in
the validation list of the cell. I have a variety of pictures that I would
like to insert into that same worksheet when that cell is changed. There is
one picture per value, but I need the picture to insert at the same spot.
Any idea's?


J

I think I would PROBABLY have ALL the pictures in the cell, one on top
of another - and when the cell is changed set the size of all but one
of them to zero, the other can be set to the normal height/width
- for example - to set Picture2 to invisible
ActiveSheet.Shapes("Picture 2").Select
Selection.ShapeRange.Height = 0#
Selection.ShapeRange.Width = 0#



Rookie_User wrote:
> I have read a few posts that come close but maybe some expert can offer a
> more definitive solution here. I have a drop down list in cell A6, it is in
> the validation list of the cell. I have a variety of pictures that I would
> like to insert into that same worksheet when that cell is changed. There is
> one picture per value, but I need the picture to insert at the same spot.
> Any idea's?
>
>
> J

Having all the pictures already inserted and in position is a good idea.

I would, however, use the visibility rather than changing the height/width.

The following would accomplish this for 3 pictures.


Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("a6")) Is Nothing Then
For Each sh In Shapes
If Left(sh.Name, 7) = "Picture" Then sh.Visible = msoFalse
Next sh
Select Case Target.Value
Case "Pic 1"
Shapes("Picture 1").Visible = msoTrue
Case "Pic 2"
Shapes("Picture 2").Visible = msoTrue
Case "Pic 3"
Shapes("Picture 3").Visible = msoTrue
Case Else
End Select
End If
End Sub



"aidan.heritage@virgin.net" wrote:

> I think I would PROBABLY have ALL the pictures in the cell, one on top
> of another - and when the cell is changed set the size of all but one
> of them to zero, the other can be set to the normal height/width
> - for example - to set Picture2 to invisible
> ActiveSheet.Shapes("Picture 2").Select
> Selection.ShapeRange.Height = 0#
> Selection.ShapeRange.Width = 0#
>
>
>
> Rookie_User wrote:
> > I have read a few posts that come close but maybe some expert can offer a
> > more definitive solution here. I have a drop down list in cell A6, it is in
> > the validation list of the cell. I have a variety of pictures that I would
> > like to insert into that same worksheet when that cell is changed. There is
> > one picture per value, but I need the picture to insert at the same spot.
> > Any idea's?
> >
> >
> > J
>
>

Wow, those are great solutions that I didn't think of at all. Can you
comment on this concept? If I were to have a worksheet called Pictures and
it had all the pictures on it and then copied and pasted them to the active
worksheet depending on the value in the cell. I think having pics on top of
pics is a pain to get to the bottom pic, because you can't grab onto it -
right? Anyway, I am going to see how yoru solution works and thank you, I
think I can put it into action.

"crazybass2" wrote:

> Having all the pictures already inserted and in position is a good idea.
>
> I would, however, use the visibility rather than changing the height/width.
>
> The following would accomplish this for 3 pictures.
>
>
> Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target.Cells, Range("a6")) Is Nothing Then
> For Each sh In Shapes
> If Left(sh.Name, 7) = "Picture" Then sh.Visible = msoFalse
> Next sh
> Select Case Target.Value
> Case "Pic 1"
> Shapes("Picture 1").Visible = msoTrue
> Case "Pic 2"
> Shapes("Picture 2").Visible = msoTrue
> Case "Pic 3"
> Shapes("Picture 3").Visible = msoTrue
> Case Else
> End Select
> End If
> End Sub
>
>
>
> "aidan.heritage@virgin.net" wrote:
>
> > I think I would PROBABLY have ALL the pictures in the cell, one on top
> > of another - and when the cell is changed set the size of all but one
> > of them to zero, the other can be set to the normal height/width
> > - for example - to set Picture2 to invisible
> > ActiveSheet.Shapes("Picture 2").Select
> > Selection.ShapeRange.Height = 0#
> > Selection.ShapeRange.Width = 0#
> >
> >
> >
> > Rookie_User wrote:
> > > I have read a few posts that come close but maybe some expert can offer a
> > > more definitive solution here. I have a drop down list in cell A6, it is in
> > > the validation list of the cell. I have a variety of pictures that I would
> > > like to insert into that same worksheet when that cell is changed. There is
> > > one picture per value, but I need the picture to insert at the same spot.
> > > Any idea's?
> > >
> > >
> > > J
> >
> >

I need some more help .

When you reference Shapes("Picture 1") --- is this the name of the picture
or if I have three pics on the worksheet - how do I differentiate between
them?

"crazybass2" wrote:

> Having all the pictures already inserted and in position is a good idea.
>
> I would, however, use the visibility rather than changing the height/width.
>
> The following would accomplish this for 3 pictures.
>
>
> Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target.Cells, Range("a6")) Is Nothing Then
> For Each sh In Shapes
> If Left(sh.Name, 7) = "Picture" Then sh.Visible = msoFalse
> Next sh
> Select Case Target.Value
> Case "Pic 1"
> Shapes("Picture 1").Visible = msoTrue
> Case "Pic 2"
> Shapes("Picture 2").Visible = msoTrue
> Case "Pic 3"
> Shapes("Picture 3").Visible = msoTrue
> Case Else
> End Select
> End If
> End Sub
>
>
>
> "aidan.heritage@virgin.net" wrote:
>
> > I think I would PROBABLY have ALL the pictures in the cell, one on top
> > of another - and when the cell is changed set the size of all but one
> > of them to zero, the other can be set to the normal height/width
> > - for example - to set Picture2 to invisible
> > ActiveSheet.Shapes("Picture 2").Select
> > Selection.ShapeRange.Height = 0#
> > Selection.ShapeRange.Width = 0#
> >
> >
> >
> > Rookie_User wrote:
> > > I have read a few posts that come close but maybe some expert can offer a
> > > more definitive solution here. I have a drop down list in cell A6, it is in
> > > the validation list of the cell. I have a variety of pictures that I would
> > > like to insert into that same worksheet when that cell is changed. There is
> > > one picture per value, but I need the picture to insert at the same spot.
> > > Any idea's?
> > >
> > >
> > > J
> >
> >

Using the "Visible" method you will only be able to select the currently
visible picture (ie. the picture cell A6 currenlty refers to. Hidden
pictures are not selectable.

Mike

"Rookie_User" wrote:

> Wow, those are great solutions that I didn't think of at all. Can you
> comment on this concept? If I were to have a worksheet called Pictures and
> it had all the pictures on it and then copied and pasted them to the active
> worksheet depending on the value in the cell. I think having pics on top of
> pics is a pain to get to the bottom pic, because you can't grab onto it -
> right? Anyway, I am going to see how yoru solution works and thank you, I
> think I can put it into action.
>
> "crazybass2" wrote:
>
> > Having all the pictures already inserted and in position is a good idea.
> >
> > I would, however, use the visibility rather than changing the height/width.
> >
> > The following would accomplish this for 3 pictures.
> >
> >
> > Sub Worksheet_Change(ByVal Target As Range)
> > If Not Intersect(Target.Cells, Range("a6")) Is Nothing Then
> > For Each sh In Shapes
> > If Left(sh.Name, 7) = "Picture" Then sh.Visible = msoFalse
> > Next sh
> > Select Case Target.Value
> > Case "Pic 1"
> > Shapes("Picture 1").Visible = msoTrue
> > Case "Pic 2"
> > Shapes("Picture 2").Visible = msoTrue
> > Case "Pic 3"
> > Shapes("Picture 3").Visible = msoTrue
> > Case Else
> > End Select
> > End If
> > End Sub
> >
> >
> >
> > "aidan.heritage@virgin.net" wrote:
> >
> > > I think I would PROBABLY have ALL the pictures in the cell, one on top
> > > of another - and when the cell is changed set the size of all but one
> > > of them to zero, the other can be set to the normal height/width
> > > - for example - to set Picture2 to invisible
> > > ActiveSheet.Shapes("Picture 2").Select
> > > Selection.ShapeRange.Height = 0#
> > > Selection.ShapeRange.Width = 0#
> > >
> > >
> > >
> > > Rookie_User wrote:
> > > > I have read a few posts that come close but maybe some expert can offer a
> > > > more definitive solution here. I have a drop down list in cell A6, it is in
> > > > the validation list of the cell. I have a variety of pictures that I would
> > > > like to insert into that same worksheet when that cell is changed. There is
> > > > one picture per value, but I need the picture to insert at the same spot.
> > > > Any idea's?
> > > >
> > > >
> > > > J
> > >
> > >

Excel gives each picture a name based on what order it is created similar to
sheets and charts. I'm unaware of a quick way to determine the name from
within Excel. Here is some code that will add the name to the Alternative
Text box. Insert the code, then double-click on any cell. Then you can
right click the pictures, select "Format Picture" and then click the "Web"
tab. You should see the name of the picture in the "Alternative Text" box.
Once this is done you can remove the bit of code and you have a permanant
reference to the picture name. If you already have a "beforeDoubleClick"
event you can just add this peice of code to it.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
For Each shp In Shapes
If Left(shp.Name, 7) = "Picture" Then
shp.AlternativeText = shp.Name
End If
Next shp
End Sub

Now that you have the names of the pictures you can use those names to
modify the visibility code.

Mike


"Rookie_User" wrote:

> I need some more help .
>
> When you reference Shapes("Picture 1") --- is this the name of the picture
> or if I have three pics on the worksheet - how do I differentiate between
> them?
>
> "crazybass2" wrote:
>
> > Having all the pictures already inserted and in position is a good idea.
> >
> > I would, however, use the visibility rather than changing the height/width.
> >
> > The following would accomplish this for 3 pictures.
> >
> >
> > Sub Worksheet_Change(ByVal Target As Range)
> > If Not Intersect(Target.Cells, Range("a6")) Is Nothing Then
> > For Each sh In Shapes
> > If Left(sh.Name, 7) = "Picture" Then sh.Visible = msoFalse
> > Next sh
> > Select Case Target.Value
> > Case "Pic 1"
> > Shapes("Picture 1").Visible = msoTrue
> > Case "Pic 2"
> > Shapes("Picture 2").Visible = msoTrue
> > Case "Pic 3"
> > Shapes("Picture 3").Visible = msoTrue
> > Case Else
> > End Select
> > End If
> > End Sub
> >
> >
> >
> > "aidan.heritage@virgin.net" wrote:
> >
> > > I think I would PROBABLY have ALL the pictures in the cell, one on top
> > > of another - and when the cell is changed set the size of all but one
> > > of them to zero, the other can be set to the normal height/width
> > > - for example - to set Picture2 to invisible
> > > ActiveSheet.Shapes("Picture 2").Select
> > > Selection.ShapeRange.Height = 0#
> > > Selection.ShapeRange.Width = 0#
> > >
> > >
> > >
> > > Rookie_User wrote:
> > > > I have read a few posts that come close but maybe some expert can offer a
> > > > more definitive solution here. I have a drop down list in cell A6, it is in
> > > > the validation list of the cell. I have a variety of pictures that I would
> > > > like to insert into that same worksheet when that cell is changed. There is
> > > > one picture per value, but I need the picture to insert at the same spot.
> > > > Any idea's?
> > > >
> > > >
> > > > J
> > >
> > >

Thanks for the change to the code - I had wanted to go with invisible,
but manually formatting the picture didn't give me that as an option,
so brain decided it couldn't be done! Silly brain!

crazybass2 wrote:
> Having all the pictures already inserted and in position is a good idea.
>
> I would, however, use the visibility rather than changing the height/width.
>
> The following would accomplish this for 3 pictures.
>
>
> Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target.Cells, Range("a6")) Is Nothing Then
> For Each sh In Shapes
> If Left(sh.Name, 7) = "Picture" Then sh.Visible = msoFalse
> Next sh
> Select Case Target.Value
> Case "Pic 1"
> Shapes("Picture 1").Visible = msoTrue
> Case "Pic 2"
> Shapes("Picture 2").Visible = msoTrue
> Case "Pic 3"
> Shapes("Picture 3").Visible = msoTrue
> Case Else
> End Select
> End If
> End Sub
>
>
>
> "aidan.heritage@virgin.net" wrote:
>
> > I think I would PROBABLY have ALL the pictures in the cell, one on top
> > of another - and when the cell is changed set the size of all but one
> > of them to zero, the other can be set to the normal height/width
> > - for example - to set Picture2 to invisible
> > ActiveSheet.Shapes("Picture 2").Select
> > Selection.ShapeRange.Height = 0#
> > Selection.ShapeRange.Width = 0#
> >
> >
> >
> > Rookie_User wrote:
> > > I have read a few posts that come close but maybe some expert can offer a
> > > more definitive solution here. I have a drop down list in cell A6, it is in
> > > the validation list of the cell. I have a variety of pictures that I would
> > > like to insert into that same worksheet when that cell is changed. There is
> > > one picture per value, but I need the picture to insert at the same spot.
> > > Any idea's?
> > >
> > >
> > > J
> >
> >