Place Picture in Excel Worksheet Cell Using VBA

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()

End Sub
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
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 ‘.


Further reading:
How to insert a picture into Excel at a specified cell position with vb6

4 thoughts on “Place Picture in Excel Worksheet Cell Using VBA

  1. tonygabani

    I have created a database in excel 2007 using VBA. I can Add or Edit any record through my user-form but my question is: is it possible to include images or picture on my database so that whenever I edit the record on my user form, the image or picture of person appear on the image box. sir your solution will help me alot

    Reply
  2. Chris

    Hello

    Is there any possibility to create similar XLS project but with relations to the file name in the folder instead pic name in Excel ?

    Thank you in advance.

    Reply
  3. SOMUNAIR

    Hai.
    I am working on a file and would like to create a file for creating the quotation & invoice.
    I have around 6000 line items and i would like to prepare the file in such way.

    IN the quotation format i would like to place a drop down As Brand Name, so upon selection of Brand name the item from a particular brand must come in the second Column Category will show the items from that Particular brand only
    And on selection of Category , i should get the Items categorized in that Head ( as below, If i select brand AAAA, in 2nd drop down it should show only Table & Chair And in second Drop down if i select “Chair” 3rd drop down i should get only ORA, EVA EDEn only.)

    And if i Select EVA chair, The Image Column should appear the picture of the chair.

    Eg:

    Brand [b] Category Items [/b]

    AAAA Tables, Table of 1600 length,
    Table of 1800 Length,
    Chairs ORA chair
    Eva chair
    BBBB Sofa, EDEN Sofa
    GEMI Sofa
    Cabinets, UC Cab 1800
    UC Cab 200
    Chairs LUCY Chair
    VIRA Chair
    CCCC Table JAZZ Table 1600
    Chair
    Sofa

    Can you guide me

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *