Navigating between sheets

There are some simple techniques for navigating between worksheets, apart from clicking on the sheet tabs at the bottom of the worksheet.

First of all, where should you put your navigation? If you have several sheets, it may be an idea to put it along the top, and freeze the first couple of rows, so even if the user scrolls down, the navigation links are still there.

Hyperlinks 

The simplest is probably to use hyperlinks.

This has the advantage that it behaves like a web page, and these days, everyone knows what a hyperlink does.

Links attached to drawing objects  

You can insert a button (or any other drawing object) from the Drawing toolbar, and if you rightclick on it, you'll se a Hyperlink option, which you can set up as shown above.

Links embedded in text  

If you want to embed a hyperlink in the middle of (say) a text box, so that if you click on a specific word and nowhere else, it will take you somewhere, you can do that too. Create a little textbox that just covers the hyperlinked words, remove its border and fill colour, so it is transparent, then set its hyperlink property. Finally, manually format the hyperlinked text to blue underlined, as a signal to the user.

Navigation with VBA  

If you can program, you can do even more cool stuff. For example, if you have a series of sheets with back and forwards navigation arrows on each sheet, you won't want to write code to handle each and every button. Here are two techniques for using a single routine to handle most or all of your navigation.

Here's code that sends the user to the sheet on the left of the current sheet.

Sub PreviousSheet()
   If ActiveSheet.Index>1 Then Sheets(ActiveSheet.Index-1).Activate
End Sub

Or you can give your navigation buttons special names which tell VBA where to go, For example, you might call them btnXXXXX, where XXXXX is the name of the sheet to go to when the button is clicked. So btnFred would navigate to the sheet called Fred. The code is below - note that Application.Caller gives the name of the button that was clicked, and all we have to do is take off the 3 letter prefix and then navigate.

Sub Navigate()
   Sheets(Mid$(Application.Caller,4).Activate
End Sub

 

Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.