How to place an appropriate picture in an Excel worksheet using VBA



How to place apicture in an Excel worksheet cell for better identification?
  1. 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.
  2. Now select the cells A2 to B4 and give a name called MyPics.
  3. Now using data validation and the offset function insert the names in cells A2 under the header 'Name' in Sheet1.
  4. In Sheet1 under the header picture in address D1 use the Vlookup function to assign an appropriate picture name.
  5. Now use the data in D2 to make visible the appropriate picture out of the many inserted pictures
  6. Right click on sheet1 and select view code
  7. 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.
  8. Next write the code as shown between
    Private Sub Worksheet_Calculate()

    End Sub
  9. 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 '.



Reference

Watch the free training video