Creating HyperLinks in VB

[Guest]

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?

 

[Dennis]

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

 

[acampbell012]

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

 

[Dennis]

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

 

[Dennis]

Forgot the final "+" sign in:

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

GL EagleOne

 

[Guest]

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

 

[acampbell]

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...

 

[acampbell]

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