You are here
Excel spreadsheet with Tab Navigator macro
Sun, 2019-01-06 18:58 — Michael Dew
Available for download is a spreadsheet which contains a macro which assists with navigating between worksheets in an Excel workbook with a large number of worksheets.
The video at https://youtu.be/LuCyml5l2YI explains basic methods for navigating between worksheets (a.k.a. tabs) in an Excel workbook using built in functionality of Excel, and then explains use of the macro (contained in the spreadsheet available for download) which assists with navigating between worksheets in an Excel workbook with a large number of worksheets.
The video also explains how to import the tab navigator macro into an existing workbook so that use can be made of the macro in a workbook that was not built from scratch in the spreadsheet available for download.
Security issues
The spreadsheet was developed using macros (programmed in visual basic) and in order for the spreadsheet to operate your security settings in Microsoft Excel must be set to allow macros to run. To enable macros in Excel 2010 (similar procedures will apply in other versions of Excel) click:
- File (i.e. the menu on the top left)
- Options
- Trust Centre
- Trust Centre Settings
- Macro settings
- Enable all macros
Macros can pose a security risk because they make your computer perform certain steps automatically, but are typically only dangerous when made to be that way by a computer programmer with a malicious intention – hence the warning when you change the security settings in Excel. However, the macros available in the spreadsheet available for download on this page are made with good intentions and should not harm your computer.
Users downloading and using this spreadsheet do so entirely at their own risk and agree to accept all risks associated with using the spreadsheet.
The spreadsheet is open and users familiar with macros can review (and modify) the code.
Omitting sheets from list of sheets
For each sheet in your workbook that you want to be omitted from the list of sheets to navigate to when the macro runs then:
- Press Alt + F11 to open the visual basic edtior.
- Select the sheet you want to be hidden so that it is highlighted in blue in the project explorer list (see image below).
- In the properties window, adjust the visibility setting to "xlSheetVeryHidden" (see image below).
