Link to an image

[DrBob]

I'm using a DGET formula to bring up data about pupils. When I type a name into a cell the formula returns data from another sheet regarding their performance.

What I'm trying to do is bring up an image of each pupil with the data. I've tried everything I can think of. Is there any way of doing this? (Images are stored in a separate folder).

Help much appreciated.

 

[MickG]

Hi, DrBob
This is one way of doing it.
The code selects the Picture name from your nominated cell, add the appropriate file ext, Retrieves that picture from your nominated folder address, and inserts it in the sheet.
The postion and size is governed by the code details that you will be able to modify.
You could also achieve the same result by inserting all you picture in the workbook.
Hiding them, then individually "Unhiding" and positioning them as required.

In this code you will have to delete each picture after use, unless you would like some code to clear the sheet of pictures prior to inserting a new picture.

Dim Nm As String, pth As String, Fpth As String

Application.ScreenUpdating = False
On Error Resume Next
 Nm = Range("a1").Value & ".jpg"
pth = "C:\Documents and Settings\test\My Documents\My Pictures\"
    Fpth = pth & Nm

ActiveSheet.Pictures.Insert(Fpth).Select
    With Selection
        .Height = 30
         .Width = 50
          .Top = Range("a1").Top
           .Left = Range("a1").Left
       End With

Application.ScreenUpdating = True

Regards Mick

 

[DrBob]

This is great, Mick. Thanks. I've been wanting to do this for ages!
If you have time, is there any chance of the code to clear the sheet of pictures prior to inserting a new picture?

 

[Legacy 98055]

I included an example you may download because it will be easier for you to understand by seeing it work...

Extract 1 workbook and 4 pictures to the same path.

AutoPupilPic.zip

Basically, you draw out a shape from the drawing toolbar(for example, a rectangle), select it, name it by selecting the name box, type "PupilPicture", hit enter.

In your database, include information that determines which picture to show when your data is pulled using whatever function. My example uses VLookUp. The picture naming convention is a concatenation of the students first and last name. You will have to provide the path to the folder containing your pictures.

You then trap the calculation event and update the shape's picture using the UserPicture method...

[e2] is the cell that contains the picture name. It is hidden in the example but there are multiple ways of doing this.

Private Sub Worksheet_Calculate()
    On Error Resume Next
    Application.EnableEvents = False
    Me.Calculate
    Me.Shapes("PupilPicture").Fill.UserPicture ThisWorkbook.Path & Application.PathSeparator & Range("E2")
    Application.EnableEvents = True
End Sub

No doubt, I made this sound more complicated than it really is. Download the example.

 

[MickG]

Hi, DrBob I think this will do it !

Dim Nm As String, pth As String, Fpth As String

Application.ScreenUpdating = False
On Error Resume Next
 Dim oPic As shape
  For Each oPic In ActiveSheet.Shapes
        If oPic.Type = 13 Then
               oPic.Delete
                 End If
                    Next oPic
 
 Nm = Range("a1").Value & ".jpg"
pth = "C:\Documents and Settings\test\My Documents\My Pictures\"
    Fpth = pth & Nm

ActiveSheet.Pictures.Insert(Fpth).Select
    With Selection
        .Height = 30
         .Width = 50
          .Top = Range("a1").Top
           .Left = Range("a1").Left
       End With


Application.ScreenUpdating = True

Regards Mick

 

[DrBob]

Thanks guys!

Some really good stuff - I've learned a hell of a lot.

Regards,
DrBob.