Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a named picture
On Jun 11, 11:58 pm, "Rob L"
wrote: 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. GoToMikeBrown01 ? 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a named picture
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. GoToMikeBrown01 ? Thanks Rob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a named picture
Thnaks Ken - I'll try this tonight.
Rob L "Ken Johnson" wrote in message ... On Jun 11, 11:58 pm, "Rob L" wrote: 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. GoToMikeBrown01 ? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy as picture. | Excel Discussion (Misc queries) | |||
Macro copy picture from web to excel | Excel Discussion (Misc queries) | |||
Copy picture and paste hyperlink | Excel Worksheet Functions | |||
copy as picture | Excel Discussion (Misc queries) | |||
XL2007 Copy As Picture | Excel Discussion (Misc queries) |