Hyperlink to cell in local document

[jonck]

Hi,
I would like to create a hyperlink that refers to a specific cell in
the local document using VBA. I have Googled intensively and read
through all entries in the help file that seemed relevant, but I was
unable to locate how I would go about doing something like that.
Could someone please point me in the right direction how I would go
about doing what I described?

Thanks very much, Jonck

 

[JE McGimpsey]

I would like to create a hyperlink that refers to a specific cell in
the local document using VBA. I have Googled intensively and read
through all entries in the help file that seemed relevant, but I was
unable to locate how I would go about doing something like that.
Could someone please point me in the right direction how I would go
about doing what I described?

One way:

Form it like any other hyperlink anchor using the octothorpe and
sheet/cell reference.

Choose Insert/Hyperlink.

Link To: #Sheet3!J7
Display: <your text here>

If you have a named range (say "MyRange"), use that instead:

Link To: #MyRange

You can also do this through the GUI - in the Insert Hyperlink dialog,
click the Document tab and click the Locate button next to the Anchor
textbox. Choose either one of the defined names, or select a sheet in
the listbox, and enter a cell reference in the textbox.

You can also specify a multiple-cell range:

Link To: #Sheet4!B2:B10

 

[jonck]

My apologies for not making myself clear. What I am looking for is the
VBA code to put a hyperlink that refers to another cell of another
worksheet in the same workbook.

So for example something like this (note that this is completely
imaginary code, it does not work):
Dim myLink as HyperLink

Set myLink = new HyperLink
myLink.linkTo = "#Sheet3!J7"
myLink.display = "Jump to your report"

Worksheets("sheet1").Cells(3,4).value = myLink

I was not able to find the correct code for doing the above, it is
unclear for me which objects I should use.

Thanks very much, Jonck

 

[JE McGimpsey]

My apologies for not making myself clear. What I am looking for is the
VBA code to put a hyperlink that refers to another cell of another
worksheet in the same workbook.

So for example something like this (note that this is completely
imaginary code, it does not work):
Dim myLink as HyperLink

Set myLink = new HyperLink
myLink.linkTo = "#Sheet3!J7"
myLink.display = "Jump to your report"

Worksheets("sheet1").Cells(3,4).value = myLink

I was not able to find the correct code for doing the above, it is
unclear for me which objects I should use.

Sorry for my previous answer - I missed the VBA reference. Whenever I've
not known what objects or methods to use, I've usually started with
recording a macro. In this case, I get something like

Public Sub Macro1()
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:="Sheet3!J7"
End Sub

The recorder always uses the Selection object (and it didn't record the
display text), but that's enough to search Help for the Hyperlinks.Add
method, where one can find the appropriate syntax, and a good example,
which helps tweak the macro to give what you're after:

Public Sub Macro1()
With Worksheets("Sheet1")
.Hyperlinks.Add _
Anchor:=.Cells(3, 4), _
Address:="", _
SubAddress:="Sheet3!J7", _
ScreenTip:="", _
TextToDisplay:="Jump to your report"
End With
End Sub


Note that hyperlinks can be the child objects of range objects, so you
could alternatively use

With Worksheets("Sheet1").Cells(3, 4)
.Hyperlinks.Add _
Anchor:=.Cells, _
Address:="", _
SubAddress:="Sheet3!J7", _
ScreenTip:="", _
TextToDisplay:="Jump to your report"
End With

Note also that VBA Help ("Hyperlink Object") says "The Hyperlink object
is a member of the Hyperlinks collection," which hints that one can't
make a standalone hyperlink object variable with New. However you could
do:

Dim myLink As Hyperlink
With Worksheets("Sheet1").Cells(4, 4)
Set myLink = .Hyperlinks.Add( _
Anchor:=.Cells, _
Address:="")
End With
myLink.SubAddress = "Sheet3!J7"
myLink.TextToDisplay = "Jump to your report"




Adding a hyperlink to a range's hyperlinks collection also adds the
hyperlink to the range's parent worksheet hyperlinks collection.

 

[jonck]

Thank you very much for your detailed answer. I also found your tip on
how you go about investigating these things very helpful.

Kind regards, Jonck