Unable to add hyperlinks using VBA to some titles of pictures stored elsewhere

[shahin]

Hi there everybody! Good day.I have some titles of pictures listed in a spreadsheet. I would like to add hyperlinks to that titles so that when i click these titles i can get the pictures which I downloaded and stored in D drive. I recorded a macro to apply it myself but the
code i got is applicable for selection range only. Now I like to make it dynamic
so that it add to the hyperlinks in a collection of range. A little push
from somebody experienced with this may help me accomplish my code. Thanks in advance. FYC, I'm pasting here the code I would like to work with.

This is what i recorded:

Sub CreateHyperlink()
    Range("B4").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "D:\Test\Images\man-on-fire-1987-1080p-poster.jpg", TextToDisplay:= _
        "man-on-fire-1987-1080p-poster.jpg"
End Sub

I tried like the below code:

Sub CreateHyperlink()

dim cel as variant

'''To be spsecific what should i declare cel as [Range, object or variant]'''

    for each cel in range("B2:B" & cells(rows.count,1).end(xlup).row)

    ActiveSheet.Hyperlinks.Add Anchor:=cel, Address:= _
        "D:\Test\Images\" & cel, TextToDisplay:= cel

    next cel

End Sub

 

[Monty]

Hello shahin

Try this attached file as per your requirment.

1) Change the path for your images folder..
2) Click on the button "Run".

Macro does.

Get your image names in the Column "A" and it's path in column "B" and creates hyperlink.

Hope you like it...Let me know any challenges.

 

[shahin]

It does create hyperlinks then and there but when I click on either of the links to open an image it shows an error mentioning "Reference is not valid". Am i doing something wrong while operating your code?

 

[shahin]

Hi Monty! Thanks for your effort. I modified my earlier code a little bit and it is working fine now. Actually i forgot to write (.value) to my cel variable as in, cel.value. This is the code i was expecting:

Sub CreateHyperlink()
Dim cel As Variant

    For Each cel In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)

    ActiveSheet.Hyperlinks.Add Anchor:=cel, Address:= _
        "D:\Test\Images\" & cel.Value, TextToDisplay:=cel.Value

    Next cel

End Sub

 

[Monty]

Hello Shahin

Thanks for sharing... Good luck.