How to place apicture in an Excel worksheet cell
for better identification?
- In the worksheet Sheet 2 create a table with the headers
'Name' and 'Picture'. Incells A2 to A4 enter the names of people or
items. In the spreadsheet cells B2 to B4 enter the names of the
pictures which you inserted in Sheet1. When you insert an image in an
Excel worksheet, the software places the image in the worksheet as a
floating picture which is not located in a specific cell and it calls
it 'picture 1'. If you insert another picture it is called 'picture 2'
and so on.
- Now select the cells A2 to B4 and give a name called MyPics.
- Now using data validation and the offset function insert
the names in cells A2 under the header 'Name' in Sheet1.
- In Sheet1 under the header picture in address D1 use the
Vlookup function to assign an appropriate picture name.
- Now use the data in D2 to make visible the appropriate
picture out of the many inserted pictures
- Right click on sheet1 and select view code
- In the new window of the Visual Basic Editor select
worksheet by clicking on the drop-down arrow next to 'General' and
select 'calculate' by clicking on the 'Calculate' option.
- Next write the code as shown between
Private Sub Worksheet_Calculate()
End Sub
- Above the 'Private Sub Worksheet_Calculate()' type 'Option
Explicit'
Dim MyPic As Picture
Me.Pictures.Visible = False
With Range("D2")
For Each MyPic In Me.Pictures
If MyPic.Name = .Text Then
MyPic.Visible = True
MyPic.Top = .Top
MyPic.Left = .Left
Exit For
End If
Next MyPic
End With
In the code we first define a variable called 'MyPic' and then we
ensure that all the inserted pictures on the worksheet are hidden or
invisible. Than we run a loop to select the appropriate picture and
place it in the specific cell 'D2' with the alignment 'top' and 'left'.
When you select a name in 'A2' you can view the relevant image. Also in
B2 and C2 we have placed the related 'phone' number and 'address '.
Watch
the vdeo
|