Scroll the menu to find all the places to visit
Batch Generating Worksheet Hyperlinks in Excel
29 Apr 2017In my work I often find myself building spreadsheets to carry out calculations, manage sophisticated processes and capture information. These spreadsheets can often extend to covering many worksheets and as a result it can be awkwardly slow to navigate to particular worksheets just by paging back and forth with the navigation buttons.
In this post I’m going to illustrate a couple of useful techniques to help you navigate around.
In-built Navigation
Excel is the Jewel in the Microsoft Office crown and has a quick access navigation helper built in. The buttons that are used for scrolling worksheets will display a pop-up dialog when you right-click on it that displays all of the worksheets available. You can also slide to the left most or right most worksheet by holding down CTRL whilst clicking on the left worksheet navigation button or the right worksheet navigation button respectively.
This is great not only because it exists but also because it is accessible regardless of what worksheet you are on.
What’s not so great is that you have to know it exists and if you pass on a large worksheet to someone they may not realise this or you might find you have to slap some instructions on the first worksheet; and let’s be honest how many recipients will think they need to read instructions for a spreadsheet.
Contents Generation
I like to include summary worksheets in my spreadsheets. The idea is simply to bring the key information to the fore and the subsequent sheets to provide the detail and workings behind that summary. Essentially a dashboarding approach. As a result I like to be able to direct people to the right worksheet by using inline hyperlinks on the cell content. This can be thought of like a table of contents.
Whilst it isn’t too onerous to set-up a few cells on the first worksheet with hyperlinks to the corresponding worksheet it becomes lee so for spreadsheets that contain any worksheets. To address this I wrote a little VBA code to generate hyperlinked lists automatically.
Add the code below into your spreadsheet and you’ll be able to run it as a macro - see this tutorial from Alexander Frolov at AbleBits if you need a hand adding and running the code.
Option Explicit
Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Const VK_CONTROL As Integer = &H11
Sub SheetNamesAndLinks()
Dim intCounter As Integer, intInitial As Integer
Dim strLocation As String
Dim rngCurrentCell As Range
'Default to including first worksheet but skip if CTRL key held down on run
intInitial = 1
If GetKeyState(VK_CONTROL) < 0 Then intInitial = 2
'Look through worksheets and add links down from (and inclusive of) currently selected cell.
For intCounter = intInitial To Sheets.Count
Set rngCurrentCell = ActiveCell.Offset(rowOffset:=intCounter - intInitial, columnOffset:=0)
rngCurrentCell = Sheets(intCounter).Name
strLocation = Sheets(intCounter).Name & "!A1"
ActiveSheet.Hyperlinks.Add Anchor:=rngCurrentCell, Address:="", SubAddress:=strLocation
Next intCounter
End Sub
The code simply cycles through each of the worksheets and gets the name. From the currently selected cell the code then inserts each worksheet name in a list extending down the page. Each worksheet name is set up as a hyperlink to navigate you to cell A1 on the corresponding worksheet when the link is selected.
Note also that the worksheet will ignore the first worksheet if you hold down the CTRL key when you run the code. This is simply because if I do have a summary worksheet set-up I always make it the first worksheet and never want to include a link to that worksheet on that worksheet.
Summary
If you work with large spreadsheets then try and make use of the in-built quick navigation dialog for getting around. If others work with your large spreadsheet consider setting them up a contents page - this code makes it practically effortless to create.
