link cell text to file of the same name

i have a workbook in excel and in one column i am putting the names of images.

i want to create a macro/vba code so that when i type in the file name into the column e.g. IMG_1234 it will automatically be hyperlinked to a file such as file:///\public\Pictures\IMG_1234.JPG

Then when someone clicks on the link it will load a new window with the picture displayed.

I have found some code that answers my question in a Macro...however I wonder if it is possible to do this so it updates automatically rather than having to run the Macro every time...

Sub PictureLink()

MyPath = "\\\public\Pictures\"
StartRow = 8
EndRow = 200
MyEnd = ".JPG"

x = 0

For i = StartRow To EndRow
    If Len(Cells(i, 4).Value) > 0 Then
        MyFileName = ""
        MyFileName = Dir(MyPath & Cells(i, 4).Text & MyEnd, vbNormal + vbDirectory)

        If MyFileName <> "" Then
            x = x + 1
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 4), _
                        Address:=MyPath & Cells(i, 4).Text & MyEnd
        End If
    End If
Next i

End Sub

Hopefully someone can help me to modify this so it does it automatically.

Thanks a lot

user157349

Posted 2012-09-10T09:00:10.580

Reputation: 33

Answers

Rename your macro like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  ' your code here
End Sub

and use Target to access the cell.

Ansgar Wiechers

Posted 2012-09-10T09:00:10.580

Reputation: 4 860

so will that automatically hyperlink the text as soon as a valid picture name is typed in the cell? – user157349 – 2012-09-10T10:54:04.413

It will try to hyperlink each changed cell, so you'll have to add some validation checks to your code to make sure that a hyperlink is only added when the value actually is an existing picture. – Ansgar Wiechers – 2012-09-10T12:16:37.533

how do i use target to access the cell? – user157349 – 2012-09-10T12:25:47.190

1worked it out now - wasn't putting the code into the sheets private function area! working perfectly now and with the code i put above only hyperlinks if there is a valid picture in the file! thanks – user157349 – 2012-09-10T12:34:13.737