Last month, you saw J. Woolley’s technique to run command files from Excel hyperlinks. He created a SuperLink function too, that is better than Excel’s HYPERLINK function. Now he’s sharing a new technique, with better hyperlinks for Excel sheets.
Better Hyperlinks for Excel Sheets
In last month’s article, J. Woolley’s created special hyperlinks that make it easy to run command files from Excel. In his new technique, SHEET::NAME , J. Wooley has created better hyperlinks for Excel sheets.
With normal Excel hyperlinks, there are limitations:
- Previous selection on the linked sheet is changed, because the hyperlink selects a specific range
- Can’t link to a chart sheet, because it doesn’t have cells
- If the linked cell is on a hidden sheet, the link fails silently
The SHEET::NAME hyperlinks overcome those limitations. These hyperlinks can:
- activate a sheet, without changing the previous selection
- activate a chart sheet, as well as worksheets
- show a message if linked cell is on a hidden sheet
Get the Sheet Name Hyperlink Files
To get started, click this link to get the zipped folder with the Sheet Name files, and download it to a folder on your computer. SheetName.zip contains the following files:
- TestSheetName.xlsm
- Sample Check Register.xlsx
- UseRunCommand.pdf
- UseSheetName.pdf
- UseSuperLink.pdf
- ExcelBugReport.pdf
- M_ProcessSheetName.bas
- M_SuperLink.bas
Install the Sheet Name Files
First, before you unzip the file, you will probably need to unblock the downloaded file using its Properties dialog box.
If you don’t unblock the file, the SHEET::NAME technique might appear to work, but it will not work correctly.
Then, after the downloaded file is unblocked, unzip all files to a single folder.
It’s important to keep the files together, because the TestSheetName.xlsm workbook assumes it is in the same folder as the other files.
Use the Sheet Name Files
After the files are unzipped, open TestSheetName.xlsm in Excel; you will probably need to Enable Content for Macros.
- NOTE: The files named M_ProcessSheetName.bas and M_SuperLink.bas have already been imported as VBA Modules. in TestSheetName.xlsm
On the Test Sheet, there are two sections with test hyperlinks.
- Static Hyperlinks, created by Insert > Link
- Dynamic Hyperlinks, created with the SuperLink function
There is a brief introduction at the top of each section, and the hyperlink text explains what each link does.
NOTE: The first link in each section opens a PDF file, with details on how the technique works.
Sheet Name Sample Sheets
There are 4 sample sheets in the TestSheetName.xlsm workbook. Three of the sample sheets (based on Excel 2016 templates) are visible:
- Home Contents Inventory List
- Inventory’s Value Chart
- Room Lookup
And there is a hidden sample sheet:
- Hidden Sheet
There’s nothing on that sheet, but it shows how the technique can work with hidden sheets too.
Test the Hyperlinks
Use the hyperlinks on the Test Sheet to test the SHEET::NAME technique — the hyperlink text explains what each link does.
For example, go to the Room Lookup sheet, and select one of the cells in the list. Then, go back to Test Sheet, and click one of the Room Lookup links.
NOTE: The links in shaded cells will not work.
The Room Lookup sheet is activated, and your previous selection on that sheet has not been changed.
Download the Sample Files
To get started, click this link to get the zipped folder with the Sheet Name files, and download it to a folder on your computer
Questions or Comments
If you have questions or comments about the Run Command Files technique, contact J. Woolley.
________________
Another way for a hyperlink to activate a sheet without specifying a cell (including a chart sheet) is discussed here:
http://dailydoseofexcel.com/archives/2018/07/02/hyperlink-formula-events/#comment-1045688