3

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

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • so will that automatically hyperlink the text as soon as a valid picture name is typed in the cell?
    – user157349
    Commented Sep 10, 2012 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
    Commented Sep 10, 2012 at 12:16
  • how do i use target to access the cell?
    – user157349
    Commented Sep 10, 2012 at 12:25
  • 1
    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
    Commented Sep 10, 2012 at 12:34

You must log in to answer this question.

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