I'd like to programatically apply a hyperlink to an active cell that will
point to a named range on another sheet in the same workbook. I haven't had
any luck with the following code:

********
Set sh = Worksheets("Dashboard")
sh.Activate
sh.Cells(p + 5, 2).Value = arrENGINE(i)
sh.Cells(p + 5, 2).Select

sh.Cells(p + 5, 2).Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=arrENGINE(i) _
, TextToDisplay:=Selection.Value
*******

where arrENGINE(i) is a string - example "GF44-123PL_G01"

Any suggestions as to what is going on?
--
Thanks!
Max

Say we have a Named Range in Sheet1 called "in_the_box"

Without VBA we can create a hyperlink to this Range with:

=HYPERLINK("#Sheet1!box_in_sheet1","to the box")

We just need the macro to create the formula for us:

Sub hyperput()

'''''''''''''''''''
'
'=HYPERLINK("#Sheet1!box_in_sheet1","to the box")
'
'''''''''''''''''''

dq = Chr(34)
s1 = "=HYPERLINK(" & dq & "#Sheet1!box_in_sheet1" & dq
s2 = "," & dq & "to the box" & dq & ")"
Selection.Formula = s1 & s2
End Sub


I dq because embedding double quotes gives me a headache

--
Gary''s Student - gsnu200722


"Max" wrote:

I'd like to programatically apply a hyperlink to an active cell that will
point to a named range on another sheet in the same workbook. I haven't had
any luck with the following code:

********
Set sh = Worksheets("Dashboard")
sh.Activate
sh.Cells(p + 5, 2).Value = arrENGINE(i)
sh.Cells(p + 5, 2).Select

sh.Cells(p + 5, 2).Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=arrENGINE(i) _
, TextToDisplay:=Selection.Value
*******

where arrENGINE(i) is a string - example "GF44-123PL_G01"

Any suggestions as to what is going on?
--
Thanks!
Max

First, that doesn't look like a valid name to me. I'd replace that hyphen with
an underscore.

and then maybe...

With sh
.Hyperlinks.Add Anchor:=.Cells(p + 5, 2), _
Address:="", _
SubAddress:=arrENGINE(i), _
TextToDisplay:=.Cells(p + 5, 2).Value
end with

(untested)

Max wrote:

I'd like to programatically apply a hyperlink to an active cell that will
point to a named range on another sheet in the same workbook. I haven't had
any luck with the following code:

********
Set sh = Worksheets("Dashboard")
sh.Activate
sh.Cells(p + 5, 2).Value = arrENGINE(i)
sh.Cells(p + 5, 2).Select

sh.Cells(p + 5, 2).Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=arrENGINE(i) _
, TextToDisplay:=Selection.Value
*******

where arrENGINE(i) is a string - example "GF44-123PL_G01"

Any suggestions as to what is going on?
--
Thanks!
Max

--

Dave Peterson