Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
so will that automatically hyperlink the text as soon as a valid picture name is typed in the cell? – user157349 Sep 10 '12 at 10:54
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 Sep 10 '12 at 12:16
how do i use target to access the cell? – user157349 Sep 10 '12 at 12:25
worked 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 Sep 10 '12 at 12:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .