Better Hyperlinks for Excel Sheets

Better Hyperlinks for Excel Sheets

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 Sheet Name hyperlinks technique, post in the Comments section below, and J. Woolley will try to help.

Better Hyperlinks for Excel Sheets http://contexturesblog.com/

________________

One thought on “Better Hyperlinks for Excel Sheets”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.