Copy a named picture

[Ken Johnson]

I have a spreadsheet (used for tracking inductions), and have one sheet set
up to print an ID Card for people. On another sheet, I have pictures of all
the people who have been inducted (yes, I know it would be better to use a
database, or to link the pictures rather than having them stored in the
sheet, but for various reason, can't do that). The pictures are all named
ie. JohnSmith01, PeterWilliams01, JohnSmith02 etc.

How do I type a picture name in a cell on the ID Card sheet, and have that
persons picture appear on the card. Alternately, how do I select a picture
by name, ie. GoTo>MikeBrown01 ?

Thanks

Rob

Maybe something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim Shp As Shape
On Error GoTo PictureNotFound
Worksheets("Sheet2").Shapes(Me.Range("A1").Value).Copy
'Delete the previous picture
For Each Shp In Me.Shapes
If Shp.Type = msoPicture Then
If Shp.TopLeftCell.Address = "$C$1" Then
If Shp.Name <> Range("A1").Value Then Shp.Delete
End If
End If
Next
Worksheets("Sheet1").Paste
With Selection
.Name = Range("A1").Value
.Top = Range("C1").Top
.Left = Range("C1").Left
End With
Range("A1").Select
Exit Sub
PictureNotFound: MsgBox "Picture not found!" & vbNewLine _
& "Check Name."
End If
End Sub

All pictures are on Sheet2.
The name of the picture to be shown on Sheet1 is typed into Sheet1 A1
and the code positions it on Sheet1 so that its top left corner is the
top left corner of C1. Just change the sheet names and cell addresses
to suit your needs.
The code needs to be pasted into the code module of the sheet showing
the chosen picture.

Ken Johnson

 

[Rob L]

I have a spreadsheet (used for tracking inductions), and have one sheet set
up to print an ID Card for people. On another sheet, I have pictures of all
the people who have been inducted (yes, I know it would be better to use a
database, or to link the pictures rather than having them stored in the
sheet, but for various reason, can't do that). The pictures are all named
ie. JohnSmith01, PeterWilliams01, JohnSmith02 etc.

How do I type a picture name in a cell on the ID Card sheet, and have that
persons picture appear on the card. Alternately, how do I select a picture
by name, ie. GoTo>MikeBrown01 ?

Thanks

Rob

 

[Rob L]

Thnaks Ken - I'll try this tonight.

Rob L

Maybe something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim Shp As Shape
On Error GoTo PictureNotFound
Worksheets("Sheet2").Shapes(Me.Range("A1").Value).Copy
'Delete the previous picture
For Each Shp In Me.Shapes
If Shp.Type = msoPicture Then
If Shp.TopLeftCell.Address = "$C$1" Then
If Shp.Name <> Range("A1").Value Then Shp.Delete
End If
End If
Next
Worksheets("Sheet1").Paste
With Selection
.Name = Range("A1").Value
.Top = Range("C1").Top
.Left = Range("C1").Left
End With
Range("A1").Select
Exit Sub
PictureNotFound: MsgBox "Picture not found!" & vbNewLine _
& "Check Name."
End If
End Sub

All pictures are on Sheet2.
The name of the picture to be shown on Sheet1 is typed into Sheet1 A1
and the code positions it on Sheet1 so that its top left corner is the
top left corner of C1. Just change the sheet names and cell addresses
to suit your needs.
The code needs to be pasted into the code module of the sheet showing
the chosen picture.

Ken Johnson