ExcelBanter - Creating HyperLinks in VB

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Creating HyperLinks in VB (https://www.excelbanter.com/excel-programming/377309-re-creating-hyperlinks-vbulletin.html)

Creating HyperLinks in VB

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

Creating HyperLinks in VB

Forgot the final "+" sign in:

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

GL EagleOne

Creating HyperLinks in VB

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

Creating HyperLinks in VB

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

Creating HyperLinks in VB

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