How to create hyperlinks with VBA to the same open workbook

Question

The setup

• I have an Excel workbook stored on my hard drive.
• The structure is such that on the first sheet I have a list of the names of the other sheets in the same workbook (...which can be created or deleted).
• All the names on the list, on the first sheet, are supposed to be hyperlinks to the corresponding sheet in the workbook. So, by clicking the name on the first sheet you jump to the corresponding sheet.
• When a new sheet is created a macro creates also the new name on the list on the first sheet and makes a hypelink of it. This works. ...BUT... The links point to the stored version of the file, not to the open workbook! Clicking the links opens the stored file and not the one which is under work.

QUESTION: How to create a hyperlink that always points to the same open workbook and not to the stored copy of it?

Answer 1

You may try creating the hyperlink as a formula (via VBA, as you need). I am using the parameters you posted, this may need a little adjustment.

Dim rngsrc as Range, rngtrgs as String
Set rngsrc = Worksheets("Summary").Cells(Cell.Row, 5)
Set rngtrg = "'" & sSheetName & "'!B5"
rngsrc.FormulaR1C1 = "=HYPERLINK(" & rngtrgs & "," & sSheetName & ")"

See

Official documentation

Example 1

Example 2

This (hopefully) answers your question. As a separate note, it still remains to be clarified why you see the behavior you see.

Answer 2

It seems that the problem comes from the following fact: My file is in a SharePoint folder. If I open it just for reading, the hyperlinks work fine. If I open the file for editing, a copy of the SharePoint file is placed on my hard disk, on a specified location. So, the path to the file is not the same as it would be if I open it read-only. Should I use hyperlink.follow to solve this?

So, this all comes down to the question: In VBA/Excel, can I create a hyperlink which always points to a location in the same opened file so that the hyperlink ignores the storage path of the corresponding file? Using empty string (or BLANK) doesn't help as the address parameter in hypelinks.add as Excel seems to automatically fill in the whole storage path.

Answer 3

Try this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "Sheet3!", TextToDisplay:="Link to sheet #3"

Address is the URL and SubAddress is a localtion of the page (or a sheet or a range in excel workbooks).

Answer 4

I upgraded to Office 2013 and "PADAM": The problem vanished! It seems that there was a bug in Excel/VBA in this in the 2007 version.