How to place an appropriate picture in an Excel worksheet cell using VBA
- In the worksheet Sheet 2 create a table with the headers ‘Name’ and ‘Picture’.
- In cells 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.
- 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()
Above the ‘Private Sub Worksheet_Calculate()’ type ‘Option Explicit’
The above line of code ensures that all the variables used in the macro are defined.
Dim MyPic As Picture
Me.Pictures.Visible = False
For Each MyPic In Me.Pictures
If MyPic.Name = .Text Then
MyPic.Visible = True
MyPic.Top = .Top
MyPic.Left = .Left
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 ‘.