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).Foll ow 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

maybe...

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



Graham F wrote:

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).Foll ow 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

--

Dave Peterson

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
--
Gary's Student


"Graham F" wrote:

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).Foll ow 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

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

"Dave Peterson" wrote:

maybe...

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



Graham F wrote:

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).Foll ow 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

--

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.

Graham F wrote:

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

"Dave Peterson" wrote:

maybe...

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



Graham F wrote:

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).Foll ow 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

--

Dave Peterson


--

Dave Peterson

Hi Dave,

Works just fine now, many thanks for your time. Best wishes, Graham

"Dave Peterson" wrote:

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.

Graham F wrote:

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

"Dave Peterson" wrote:

maybe...

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



Graham F wrote:

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).Foll ow 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

--

Dave Peterson


--

Dave Peterson

Hi Gary"s Student,

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

Best wishes

Graham

"Gary''s Student" wrote:

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
--
Gary's Student


"Graham F" wrote:

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).Foll ow 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