software

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(quot;E16quot;).Hyperlinks.Item(1).Foll ow but of course
the concatenated string is not a hyperlink. Any help amp; advice would be
appreciated. As you might guess I am new to this. Many thanks Grahammaybe...

ActiveWorkbook.FollowHyperlink _
Address:=Worksheets(1).Range(quot;E16quot;).Hyperlinks(1). Address
Graham F wrote:
gt;
gt; I need to make a hyperlink year independant, I want to concatenate a string,
gt; place it in a cell and access that from VBA. I thought this instruction might
gt; be useful, Worksheets(1).Range(quot;E16quot;).Hyperlinks.Item(1).Foll ow but of course
gt; the concatenated string is not a hyperlink. Any help amp; advice would be
gt; 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:=quot;www.google.comquot;, TextToDisplay:=quot;Googlequot;
End SubHow 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(quot;B3quot;), Address:=s,
TextToDisplay:=quot;Goodwordsquot;
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 Studentquot;Graham Fquot; wrote:

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

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

quot;Dave Petersonquot; wrote:

gt; maybe...
gt;
gt; ActiveWorkbook.FollowHyperlink _
gt; Address:=Worksheets(1).Range(quot;E16quot;).Hyperlinks(1). Address
gt;
gt;
gt;
gt; Graham F wrote:
gt; gt;
gt; gt; I need to make a hyperlink year independant, I want to concatenate a string,
gt; gt; place it in a cell and access that from VBA. I thought this instruction might
gt; gt; be useful, Worksheets(1).Range(quot;E16quot;).Hyperlinks.Item(1).Foll ow but of course
gt; gt; the concatenated string is not a hyperlink. Any help amp; advice would be
gt; gt; appreciated. As you might guess I am new to this. Many thanks Graham
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

ActiveWorkbook.FollowHyperlink _
Address:=Worksheets(1).Range(quot;e16quot;).Value

or

ActiveWorkbook.FollowHyperlink _
Address:=quot;quot; amp; Worksheets(1).Range(quot;e16quot;).Value

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

Graham F wrote:
gt;
gt; Hi Dave, Many thanks for the sugestion, when I enter the code it comes up
gt; with the subscript out of range error No 9? Any suggestions as worksheets 1
gt; exists as does cell e16! Graham
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; maybe...
gt; gt;
gt; gt; ActiveWorkbook.FollowHyperlink _
gt; gt; Address:=Worksheets(1).Range(quot;E16quot;).Hyperlinks(1). Address
gt; gt;
gt; gt;
gt; gt;
gt; gt; Graham F wrote:
gt; gt; gt;
gt; gt; gt; I need to make a hyperlink year independant, I want to concatenate a string,
gt; gt; gt; place it in a cell and access that from VBA. I thought this instruction might
gt; gt; gt; be useful, Worksheets(1).Range(quot;E16quot;).Hyperlinks.Item(1).Foll ow but of course
gt; gt; gt; the concatenated string is not a hyperlink. Any help amp; advice would be
gt; gt; gt; appreciated. As you might guess I am new to this. Many thanks Graham
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Hi Dave,

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

quot;Dave Petersonquot; wrote:

gt; So the name of the hyperlink is just the value in the cell?
gt;
gt; ActiveWorkbook.FollowHyperlink _
gt; Address:=Worksheets(1).Range(quot;e16quot;).Value
gt;
gt; or
gt;
gt; ActiveWorkbook.FollowHyperlink _
gt; Address:=quot;quot; amp; Worksheets(1).Range(quot;e16quot;).Value
gt;
gt; If that doesn't work, post back with the value that's in that cell.
gt;
gt; Graham F wrote:
gt; gt;
gt; gt; Hi Dave, Many thanks for the sugestion, when I enter the code it comes up
gt; gt; with the subscript out of range error No 9? Any suggestions as worksheets 1
gt; gt; exists as does cell e16! Graham
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; maybe...
gt; gt; gt;
gt; gt; gt; ActiveWorkbook.FollowHyperlink _
gt; gt; gt; Address:=Worksheets(1).Range(quot;E16quot;).Hyperlinks(1). Address
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Graham F wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I need to make a hyperlink year independant, I want to concatenate a string,
gt; gt; gt; gt; place it in a cell and access that from VBA. I thought this instruction might
gt; gt; gt; gt; be useful, Worksheets(1).Range(quot;E16quot;).Hyperlinks.Item(1).Foll ow but of course
gt; gt; gt; gt; the concatenated string is not a hyperlink. Any help amp; advice would be
gt; gt; gt; gt; appreciated. As you might guess I am new to this. Many thanks Graham
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Hi Garyquot;s Student,

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

Best wishes

Graham

quot;Gary''s Studentquot; wrote:

gt; Inserting a hyperlink in a cell is easy:
gt;
gt; Sub hyperset()
gt; ActiveSheet.Hyperlinks.Add Anchor:=Selection,
gt; Address:=quot;www.google.comquot;, TextToDisplay:=quot;Googlequot;
gt; End Sub
gt;
gt;
gt; How about two steps:
gt;
gt; 1. Put you concatenated string in the worksheet (say cell B2)
gt;
gt; 2. Enter and run:
gt;
gt; Sub hyperset2()
gt; dim s as string
gt; s=cells(2,2).value
gt; ActiveSheet.Hyperlinks.Add Anchor:=Range(quot;B3quot;), Address:=s,
gt; TextToDisplay:=quot;Goodwordsquot;
gt; End Sub
gt;
gt; So:
gt; B2 has your concocted (concatenated) URL
gt; B3 has the inserted hyperlink
gt;
gt; B3 can be followed by a click or from VBA
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Graham Fquot; wrote:
gt;
gt; gt; I need to make a hyperlink year independant, I want to concatenate a string,
gt; gt; place it in a cell and access that from VBA. I thought this instruction might
gt; gt; be useful, Worksheets(1).Range(quot;E16quot;).Hyperlinks.Item(1).Foll ow but of course
gt; gt; the concatenated string is not a hyperlink. Any help amp; advice would be
gt; gt; appreciated. As you might guess I am new to this. Many thanks Graham
gt; gt;