Jumping to a Specific Cell Using a Hyperlink

Hi All,

I can confirm that the hyperlink "bookmark" bug mentioned in this thread exists in Excel 2016.

I used VBA to generate a hyperlink in a cell in Workbook1 that links to another cell in Workbook2. Normally, this is quite simple to setup, either using the Excel Hyperlink Tool or suitable VBA code. I used the latter below, in a test sub to try and determine why it was not functioning as expected.

So, the error that I am getting is that after the VBA creates the hyperlink in a cell in Workbook2, when closed and re-opened and the hyperlink selected, it opens Workbook1 correctly but incorrectly at the last cell and on the last 'sheet' that was saved for the workbook.


The syntax that the VBA Hyperlink.Add method sets up is:

C:\path_to_Workbook1#'sheetname'!G15

The 'sheet-name' is enclosed by single quotes because the name contains a 'dash' without which it would flag a different error.

This syntax works perfectly in Excel 2009 but fails as described above in Excel 2016.

is there an update to Excel 2016 that addresses this bug?

Sub Test_hLink()

Dim hLinkCell As String
Dim WICSpath As String
Dim hRowNum As Integer
Dim Arr(20, 6) As String
Dim subAddRow As Integer

caseNum = 1
hRowNum = 9 + caseNum
Arr(1, 1) = "TG-A"
Arr(1, 2) = 14
Arr(1, 3) = "..\..\Workbook1.xlsm"

hLinkCell = "B" & CStr(hRowNum)
AppPath = Arr(1, 3)
subAddRow = Arr(1, 2) + caseNum
With Worksheets("Sheet1")
.Hyperlinks.Add Anchor:=.Range(hLinkCell), _
Address:=AppPath, _
SubAddress:="'" & Arr(1, 1) & "'!G" & CStr(subAddRow), _
ScreenTip:="Return to originating Sheet", _
TextToDisplay:="A100.1.1"
End With

End Sub

@Philip

The direction wasn't clear from your question. In your Excel workbook name the cell you want to link to, copy it, go to the place in your Word document, Paste Special, and on the Paste Special dialog select Paste link and Microsoft Excel Worksheet Object. Whatever is in the cell will then be displayed in your document. Naming the cell should prevent the link being broken if you later add or delete rows/columns above/left of the cell.

@Ed, I am looking to link to a specific location in a WORD document, not an Excel workbook ...

Thanks to all for replying to my query. I had developed the link in each of the ways described and while it performed properly on some versions of Excel the problem still occurs with Excel for Mac v. 15.23. Strangely, when I updated to v. 15.27 for Mac the problem disappeared. Answer: do not rely on new versions from Microsoft - test thoroughly - especially versions for the Mac which do not seem to get the same rigorous testing before release.
Thanks again. Love the service and support from all
Bob

The Hyperlink tool works for me but the first Hyperlink function example won't work in Excel 2010. I'm trying to put a reference to cell H1 on the sheet "P&L". Following the first example I write =HYPERLINK("#P&L!H1","Jump To P&L H1") and get error "Reference is not valid". I tried "P&L!H1", "'P&L'!H1" and "[P&L]!H1" but each time I get "Cannot open the specified file".

@Robert
Dear Robert I followed the steps exacly as you and MaIR describe many-many times. I'm afraid to inform you that my Excel-System didn't perform as expected...
Anyway thank you a lot.

I want to create a hyperlink to a hidden sheet. Can I do this without a macro? above method doesn't appear to work when the targeted sheet is hidden...

@Philip
Create a bookmark in your Word document at the place you want to link to. In Excel, use the Insert Hyperlink dialog to locate the Word document so that its address appears in the Address box, and then add # followed by the bookmark name. For example, if your bookmark name is ExcelLink you end up with an address like:

d:Mydocument.docx#ExcelLink

Nikojohn
Right click on the cell as if you are going to move it then press the alt key.When you move the cell to the worksheet tab you want to create the hyperlink, it activates that sheet. Release the alt key. Move cursor to location on the sheet you want the hyperlink and release. A menu will pop up. Select "Create Hyperlink". Done.

@MaIR : I use MS Office Pro Plus 10 and I tried too many times to create an hyperlink useing your way without result. Is there something wrong ?

To get a hyperlink to appear in sheet 1 and link to one in a cell in sheet 2 do the following.
Right click on the frame of the cell in sheet 2. Hold down Alt and drag it to the worksheet tab of sheet 1. This allows you to enter sheet 1. Release the Alt. Keep dragging it up to the cell where you want the hyperlink displayed. Release the mouse. Click on the option in the drop down of "Create a hyperlink here". Make sure you are pointed exactly in the centre of the cell or it may not work.
The cell in sheet 1 will have a hyperlink linking to the cell in sheet 2. I use this quite a lot to save time.

On a related question, is it also possible to link a cell to a specific location in an MS Word document ? I have only found the possibility to link to the Word document as a whole ... but probably I missed something ?

In the Name Manager (under the Formulas tab) you can create a meaningful name for your link and then use that in the Hyperlink tool. In the Hyperlink tool just select the created name in the box "Or select a place in this document" under "Defined Names".