Using a formula to return an image

[bark01]

Hi,

I have a table or date that returns company names based on ranks for a number of different criteria.

After this has happened someone else goes through my table and pastes an image file of each companies logo over the name. I'm sure there must be a way to automate this?

I've been trying to use this guide http://www.mcgimpsey.com/excel/lookuppics.html. but I can't get it to work.

The code its uses is below

Option Explicit

    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("d4")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub

It returns the right value (if it used to return company 1 it now returns the text 'picture 1') and it also hides the pictures I have inserted on to the page but doesn't then return an image.

I have 3 questions

How do I see the images again / where have they been hidden?

How do I see the name of the image file in excel - the images were .png files that were named picture 1, etc. but are these names pulled though into excel?

I'm not sure if its going to do what I want as I want it to place images in numerous locations not just one, so is there a better way to do this?

thanks.

 

[bark01]

Okay I've figured the part I was missing the image name is in the cell reference box in the top left.

But as I thought it only works with the one cell, can I just repeat the code but change the cell reference With Range("d4") for each cell I want to change or is there a better way?

I tried the below but it fails on an ambiguous name detected on the second private sub.

Option Explicit

    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
            With Range("d4")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub
    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
            With Range("d6")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub

Also if i want the picture to be centre aligned how do I do it, i presume its this part of code

oPic.Top = .Top

But don't know what it should be, I think ive tried the obvious choices .Middle , .Centre .Center.

 

[VoG]

Maybe like this

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim c As Range
Me.Pictures.Visible = False
For Each c In Range("D4,D6,D8,D10")
    With c
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = .Top
                oPic.Left = .Left
                Exit For
            End If
        Next oPic
    End With
Next c
End Sub