Hi!

I'm using the following code to try and create automatic HyperLinks in my
workbook:

Sheet2.Cells(i + 5, 2).Value = strENGINE
Sheet2.Cells(i + 5, 2).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & strENGINE & "'!A1", TextToDisplay:=strENGINE
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With

where strENGINE is the name of a worksheet in the same workbook. However,
when I click on the created link, I get a "Reference is not valid." error.
What can I do to correct this?

Note, strENGINE may contain a hypen in it - does this cause issues as well?
--
Thanks!
Max

Max,

Adapt the following for your use. Be careful with the _ at the
end of the lines


Activesheet.Hyperlinks.Add Anchor:= YourLinkCell.address, _
Address:="", SubAddress:= "'" + MyPreviousWorksheet.Name + "'!" _
+ Replace(YourLinkCell.Cells.Address, "$", ""), TextToDisplay:="'" _
+ YourLinkCell.Cells.Formula

Notice how the sheetname is concatenated. Also, how the Absolute sign
"$" is removed from the address. I chose the to show the Formula as
the blue link text display. Of course you could use
YourLinkCell.Cells.Value

HTH EagleOne


Max wrote:
> Hi!
>
> I'm using the following code to try and create automatic HyperLinks in my
> workbook:
>
> Sheet2.Cells(i + 5, 2).Value = strENGINE
> Sheet2.Cells(i + 5, 2).Select
> ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
> "'" & strENGINE & "'!A1", TextToDisplay:=strENGINE
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 14
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleSingle
> .ColorIndex = 5
> End With
>
> where strENGINE is the name of a worksheet in the same workbook. However,
> when I click on the created link, I get a "Reference is not valid." error.
> What can I do to correct this?
>
> Note, strENGINE may contain a hypen in it - does this cause issues as well?
> --
> Thanks!
> Max

Added a few lines and insterted a few "" to make it work for me. I
only get the error when the sheet "strENGINE" does not exist.

Sub mytest5()
Dim i
i = 4
Sheets(2).Cells(i + 5, 2).Value = "strENGINE"
Sheets(2).Cells(i + 5, 2).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
"'" & "strENGINE" & "'!A1", TextToDisplay:="strENGINE"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With
End Sub


Max wrote:
> Hi!
>
> I'm using the following code to try and create automatic HyperLinks in my
> workbook:
>
> Sheet2.Cells(i + 5, 2).Value = strENGINE
> Sheet2.Cells(i + 5, 2).Select
> ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
> "'" & strENGINE & "'!A1", TextToDisplay:=strENGINE
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 14
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleSingle
> .ColorIndex = 5
> End With
>
> where strENGINE is the name of a worksheet in the same workbook. However,
> when I click on the created link, I get a "Reference is not valid." error.
> What can I do to correct this?
>
> Note, strENGINE may contain a hypen in it - does this cause issues as well?
> --
> Thanks!
> Max

You appear to be attempting to use strENGINE as a variable.
>From what I can see, you have set only Sheets(2).Cells(i + 5, 2).Value
to "strENGINE."

Consider changing "'" & "strENGINE" & "'!A1" to
"'" + Sheets(2).Cells(i + 5, 2).Value "'!"

And TextToDisplay:="strENGINE" to
Sheets(2).Cells(i + 5, 2).Value

EagleOne

(E-Mail Removed) wrote:
> Added a few lines and insterted a few "" to make it work for me. I
> only get the error when the sheet "strENGINE" does not exist.
>
> Sub mytest5()
> Dim i
> i = 4
> Sheets(2).Cells(i + 5, 2).Value = "strENGINE"
> Sheets(2).Cells(i + 5, 2).Select
> ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
> SubAddress:= _
> "'" & "strENGINE" & "'!A1", TextToDisplay:="strENGINE"
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 14
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleSingle
> .ColorIndex = 5
> End With
> End Sub
>
>
> Max wrote:
> > Hi!
> >
> > I'm using the following code to try and create automatic HyperLinks in my
> > workbook:
> >
> > Sheet2.Cells(i + 5, 2).Value = strENGINE
> > Sheet2.Cells(i + 5, 2).Select
> > ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
> > "'" & strENGINE & "'!A1", TextToDisplay:=strENGINE
> > With Selection.Font
> > .Name = "Arial"
> > .FontStyle = "Regular"
> > .Size = 14
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleSingle
> > .ColorIndex = 5
> > End With
> >
> > where strENGINE is the name of a worksheet in the same workbook. However,
> > when I click on the created link, I get a "Reference is not valid." error.
> > What can I do to correct this?
> >
> > Note, strENGINE may contain a hypen in it - does this cause issues as well?
> > --
> > Thanks!
> > Max

Forgot the final "+" sign in:

"'" + Sheets(2).Cells(i + 5, 2).Value + "'!"

GL EagleOne

Thanks for the help!

I made the suggested changes - and really, it's a matter of preference...
But I'm still getting errors.

Yes, strENGINE is a variable that is previously used to name a sheet. When
I run, no erros. But when I try and click the hyperlink, I get the following
error:

"Your formula contains an invalid external reference to a worksheet. Verify
that the path, workbook, and range name or cell reference are correct, and
try again."

Here's the kicker, when I just insert a hyperlink, the path that it
generates and displays when I hover over the link is EXACTLY THE SAME!!!
What am I missing?

Here's what I have now:

Sheets(2).Cells(i + 5, 2).Value = strENGINE
Sheets(2).Cells(i + 5, 2).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" + Sheets(2).Cells(i + 5, 2).Value + "'!",
TextToDisplay:=Sheets(2).Cells(i + 5, 2).Value
With Cells(i + 5, 2).Font
.Name = "GE Inspira"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With

--
Thanks!
Max


"Dennis" wrote:

> Forgot the final "+" sign in:
>
> "'" + Sheets(2).Cells(i + 5, 2).Value + "'!"
>
> GL EagleOne
>
>

Max,

I looked at your code again. Below is a revision to what I posted
previously. As I mentioned before, Ignore the mods. I put in for
testing. Your link as coded produces the following (on my machine),
resulting in the reference error.

\\\C:\My documents\VBA Projects\Book1.xls - strENGINE! - Click once
to...

I modified code per below which links to cell A1 on sheet strENGINE.

\\\C:\My documents\VBA Projects\Book1.xls - strENGINE!A1 - Click once
to...

Max wrote:
> Thanks for the help!
>
> I made the suggested changes - and really, it's a matter of preference...
> But I'm still getting errors.
>
> Yes, strENGINE is a variable that is previously used to name a sheet. When
> I run, no erros. But when I try and click the hyperlink, I get the following
> error:
>
> "Your formula contains an invalid external reference to a worksheet. Verify
> that the path, workbook, and range name or cell reference are correct, and
> try again."
>
> Here's the kicker, when I just insert a hyperlink, the path that it
> generates and displays when I hover over the link is EXACTLY THE SAME!!!
> What am I missing?
>
> Here's what I have now:
>
> Sheets(2).Cells(i + 5, 2).Value = strENGINE
> Sheets(2).Cells(i + 5, 2).Select
> ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
> "'" + Sheets(2).Cells(i + 5, 2).Value + "'!",
> TextToDisplay:=Sheets(2).Cells(i + 5, 2).Value
> With Cells(i + 5, 2).Font
> .Name = "GE Inspira"
> .FontStyle = "Regular"
> .Size = 14
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = True
> .Underline = xlUnderlineStyleSingle
> .ColorIndex = 5
> End With
>
> --
> Thanks!
> Max
>
>
> "Dennis" wrote:
>
> > Forgot the final "+" sign in:
> >
> > "'" + Sheets(2).Cells(i + 5, 2).Value + "'!"
> >
> > GL EagleOne
> >
> >

Max,

Sorry. I meant to post the code as well.

Alan

Sub test5()
Dim strEngine
Dim i
i = 4
Sheets(2).Cells(i + 5, 2).Value = "strEngine"
Sheets(2).Cells(i + 5, 2).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
"'" + Sheets(2).Cells(i + 5, 2).Value + "'!A1", _ '<<<<<<
Inserted Cell reference.
TextToDisplay:=Sheets(2).Cells(i + 5, 2).Value
With Cells(i + 5, 2).Font
.Name = "GE Inspira"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With
End Sub


Max wrote:
> Thanks for the help!
>
> I made the suggested changes - and really, it's a matter of preference...
> But I'm still getting errors.
>
> Yes, strENGINE is a variable that is previously used to name a sheet. When
> I run, no erros. But when I try and click the hyperlink, I get the following
> error:
>
> "Your formula contains an invalid external reference to a worksheet. Verify
> that the path, workbook, and range name or cell reference are correct, and
> try again."
>
> Here's the kicker, when I just insert a hyperlink, the path that it
> generates and displays when I hover over the link is EXACTLY THE SAME!!!
> What am I missing?
>
> Here's what I have now:
>
> Sheets(2).Cells(i + 5, 2).Value = strENGINE
> Sheets(2).Cells(i + 5, 2).Select
> ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
> "'" + Sheets(2).Cells(i + 5, 2).Value + "'!",
> TextToDisplay:=Sheets(2).Cells(i + 5, 2).Value
> With Cells(i + 5, 2).Font
> .Name = "GE Inspira"
> .FontStyle = "Regular"
> .Size = 14
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = True
> .Underline = xlUnderlineStyleSingle
> .ColorIndex = 5
> End With
>
> --
> Thanks!
> Max
>
>
> "Dennis" wrote:
>
> > Forgot the final "+" sign in:
> >
> > "'" + Sheets(2).Cells(i + 5, 2).Value + "'!"
> >
> > GL EagleOne
> >
> >