Hyperlinks

G

Graham F

I need to make a hyperlink year independant, I want to concatenate a string,
place it in a cell and access that from VBA. I thought this instruction might
be useful, Worksheets(1).Range("E16").Hyperlinks.Item(1).Follow but of course
the concatenated string is not a hyperlink. Any help & advice would be
appreciated. As you might guess I am new to this. Many thanks Graham
 
D

Dave Peterson

maybe...

ActiveWorkbook.FollowHyperlink _
Address:=Worksheets(1).Range("E16").Hyperlinks(1).Address
 
G

Gary''s Student

Inserting a hyperlink in a cell is easy:

Sub hyperset()
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:="http://www.google.com", TextToDisplay:="Google"
End Sub


How about two steps:

1. Put you concatenated string in the worksheet (say cell B2)

2. Enter and run:

Sub hyperset2()
dim s as string
s=cells(2,2).value
ActiveSheet.Hyperlinks.Add Anchor:=Range("B3"), Address:=s,
TextToDisplay:="Goodwords"
End Sub

So:
B2 has your concocted (concatenated) URL
B3 has the inserted hyperlink

B3 can be followed by a click or from VBA
 
G

Graham F

Hi Dave, Many thanks for the sugestion, when I enter the code it comes up
with the subscript out of range error No 9? Any suggestions as worksheets 1
exists as does cell e16! Graham
 
D

Dave Peterson

So the name of the hyperlink is just the value in the cell?

ActiveWorkbook.FollowHyperlink _
Address:=Worksheets(1).Range("e16").Value

or

ActiveWorkbook.FollowHyperlink _
Address:="http://" & Worksheets(1).Range("e16").Value

If that doesn't work, post back with the value that's in that cell.
 
G

Graham F

Hi Gary"s Student,

Many thanks for you help, works fine in Excel 2000.

Best wishes

Graham
 
Top