Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 1 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.