Populate a pictture when a name is selected
 

I am trying to have a picture from one worksheet populate when a name is
selected from a list. In other words, if I select Jane Doe, I would like her
picture to appear in a cell above. I have tried an IF statement as well as a
vlookup, I cannot get it to work. Any suggestions? Thanks!

Populate a pictture when a name is selected
 

P.M. Naughton,

You can do that using event code.

Copy the code below, right click the sheet tab, select "View Code" and paste the code into the
window that appears.

I've assumed that your pictures are stored on a sheet named "Pictures" and that Jane Doe's picture
is named "Jane Doe", and that the cell you want to enter the name into is cell A2, so that the
picture appears in cell A1.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myShape As Shape
Dim SC As Range
Dim mySh As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Target.Address < "$A$2" Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

Set mySht = ActiveSheet

On Error Resume Next
For Each myShape In mySht.Shapes
If myShape.Name Like "*Final" Then myShape.Delete
Next myShape

Worksheets("Pictures").Select
ActiveSheet.Shapes(Target.Value).Select
Selection.Copy
mySht.Select
Target.Offset(-1, 0).Select
ActiveSheet.Paste
Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final"
Target.Select

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

"pmnaughton" wrote in message
...
I am trying to have a picture from one worksheet populate when a name is
selected from a list. In other words, if I select Jane Doe, I would like her
picture to appear in a cell above. I have tried an IF statement as well as a
vlookup, I cannot get it to work. Any suggestions? Thanks!

Populate a pictture when a name is selected
 

1. put all the pictures on a worksheet

2. assign each picture a name and record the name in some column, say column G

3. move the pictures on top of each other (like cards in a deck)

4. put data validation in a cell, say A1, that allows the user to pick a
name from the list on column G

5. in a standard module, put the following macro:
Sub revealOne()
Dim p As Shape
i = 1
For Each p In ActiveSheet.Shapes
If p.Name = Range("A1").Value Then
p.Visible = True
Else
p.Visible = False
End If
Next
End Sub

6. in the worksheet code area put the following event macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Call revealOne
End Sub

When the user picks a name after clicking on A1, the correct picture will
appear.

--
Gary''s Student - gsnu200826


"pmnaughton" wrote:

I am trying to have a picture from one worksheet populate when a name is
selected from a list. In other words, if I select Jane Doe, I would like her
picture to appear in a cell above. I have tried an IF statement as well as a
vlookup, I cannot get it to work. Any suggestions? Thanks!

Populate a pictture when a name is selected
 

And to control the position of the picture more closely, use code like this... Replace this:

Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final"

With this:

Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final"
Selection.Top = Target.Offset(-1, 0).Top + 10
Selection.Left = Target.Offset(-1, 0).Left + 10

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
P.M. Naughton,

You can do that using event code.

Copy the code below, right click the sheet tab, select "View Code" and paste the code into the
window that appears.

I've assumed that your pictures are stored on a sheet named "Pictures" and that Jane Doe's picture
is named "Jane Doe", and that the cell you want to enter the name into is cell A2, so that the
picture appears in cell A1.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myShape As Shape
Dim SC As Range
Dim mySh As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Target.Address < "$A$2" Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

Set mySht = ActiveSheet

On Error Resume Next
For Each myShape In mySht.Shapes
If myShape.Name Like "*Final" Then myShape.Delete
Next myShape

Worksheets("Pictures").Select
ActiveSheet.Shapes(Target.Value).Select
Selection.Copy
mySht.Select
Target.Offset(-1, 0).Select
ActiveSheet.Paste
Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final"
Target.Select

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

"pmnaughton" wrote in message
...
I am trying to have a picture from one worksheet populate when a name is
selected from a list. In other words, if I select Jane Doe, I would like her
picture to appear in a cell above. I have tried an IF statement as well as a
vlookup, I cannot get it to work. Any suggestions? Thanks!