Build Navigation Buttons with Excel Macros
The most common use of macros is in navigation. Workbooks that have many worksheets or tabs can be frustrating to navigate. To help your audience, you can create a switchboard, like the one shown here. When users click the Example 1 button, for example, they’re taken to the Example 1 sheet.
Creating a macro to navigate to a sheet is quite simple:
Start at the sheet that will become your starting point.
Start recording a macro.
While recording, click the destination sheet (the sheet this macro will navigate to).
Add a button form control on your starting point and Assign the macro to a button by selecting your newly recorded macro in the Assign Macro dialog box.
Excel has a built-in hyperlink feature, which enables you to convert the contents of a cell to a hyperlink that links to another location. That location can be a separate Excel workbook, a website, or another tab in the current workbook. Although creating a hyperlink may be easier than setting up a macro, you can’t apply a hyperlink to a form control (such as a button). Instead of a button, you use text to let users know where they’ll go when they click the link.