Hyperlinking & Identifying Hidden sheets in Excel

[MrExcenTric]

Hi,

I have the following problem with hidden worksheets I'd like to submit to some more experienced Excel users.

I've created a ToC of all worksheets in my Excel file, sth. for which a solution can easily be found online.
The resulting ToC displays in a dedicated sheet, as a column with the sheet names in subsequent cells.
Next I've hyperlinked these cells, so that clicking the cell jumps directly to the corresponding sheet. Which is handy, especially when there are close to 50 sheets.
My problem is that the ToC table can also include hidden sheets, and that it seems the Hyperlink() function ignores these, without even returning a warning or sth. of the kind. Clicking such a hyperlink to a hidden sheet simply does nothing, as if the link were not valid. I hoped to use this error message as a trigger to mark the ToC cells which in fact represent hidden sheets. In the same line of thinking I cannot seem to find another Excel function which would return some error or warning that might be taken advantage of in order to identify those hidden sheets.

As the ToC feature can also be found implemented with VBA, I guess my problem can easily be solved with VBA, but I'd like to avoid this for the time being, as I have zilch knowledge (yet) of VBA, and would prefer to understand what I'm doing.
If the answer is that by sticking to classical functionality I've reached Excel 2016's limits and that this cannot be achieved, that's too bad, but at least I'd know I'd have to move on to some (basic) VBA in the near future.

BTW, I have not been able to find a thorough online discussion on this "hidden" sheet status, which seems very badly documented. This forum also proposes some older threads involving hidden sheets, but none seems related to the current problem.

Thx for your time and any feedback,

MrExcenTric
(Excel 2016; Win10 AU, x64)

 

[Trevor_S]

I'm not sure how you've created your TOC? If its without VBA, I'm guessing that you've manually entered every sheet name and created each hyperlink?

Personally, I would use VBA. I would have two macros, macro 1 would create/update the TOC by simply deleting the contents of the TOC sheet, then listing all workbook sheets in column A. Macro 2 would be activated when you click any sheet name in column A - if the sheet is visible it would take you there, if it was hidden or non-existent (i.e. deleted or renamed) it would display an appropriate message.

This way, you don't need the hyperlinks...

I'm happy to write the VBA if it would help. All that I would need to know is the name of the sheet containing the TOC, what cell the list of sheet names would start in, and whether there's anything else on the TOC sheet that shouldn't be deleted when the TOC is created/refreshed.

You could in theory make the create/refresh macro run automatically every time a new sheet is created or a different sheet is selected. This would prevent the need for you to manually update the TOC, but may cause a (very brief) pause each time you select a different worksheet - as the TOC would refresh regardless of whether any changes had been made or not.

 

[Trevor_S]

Another option with the VBA solution would be for it to not even include the hidden sheets in the TOC...

 

[Trevor_S]

Here are macros based on automatic updating of the TOC every time that you select the TOC sheet. They need to go in the section of the project for the TOC sheet:

They assume that the TOC sheet is called "TOC", and that it doesn't contain anything other than the list of sheets.
If you want to include hidden sheets in the TOC:

Sub UpdateTOC()
Sheets("TOC").UsedRange.ClearContents
TocRow = 1
For TocSheet = 1 To Sheets.Count
    Sheets("TOC").Range("A" & TocRow).Value = Sheets(TocSheet).Name
    TocRow = TocRow + 1
Next
End Sub

Private Sub Worksheet_Activate()
UpdateTOC
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Value <> "" Then
    On Error GoTo CantFindSheet
        If Sheets(Target.Value).Visible = False Then
            MsgBox ("Selected sheet is hidden")
            Exit Sub
        Else
            Sheets(Target.Value).Activate
        End If
    On Error GoTo 0
End If
Exit Sub
CantFindSheet:
MsgBox ("Can't find selected sheet - Table of contents will be updated")
UpdateTOC
End Sub

If you want to exclude hidden sheets from the TOC:

Sub UpdateTOC()
Sheets("TOC").UsedRange.ClearContents
TocRow = 1
For TocSheet = 1 To Sheets.Count
    If Sheets(TocSheet).Visible = True Then
        Sheets("TOC").Range("A" & TocRow).Value = Sheets(TocSheet).Name
        TocRow = TocRow + 1
    End If
Next
End Sub

Private Sub Worksheet_Activate()
UpdateTOC
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Value <> "" Then
    On Error GoTo CantFindSheet
        If Sheets(Target.Value).Visible = False Then
            MsgBox ("Selected sheet is hidden")
            Exit Sub
        Else
            Sheets(Target.Value).Activate
        End If
    On Error GoTo 0
End If
Exit Sub
CantFindSheet:
MsgBox ("Can't find selected sheet - Table of contents will be updated")
UpdateTOC
End Sub

Both have three macros. "UpdateTOC" is the code that actually creates/updates the list of sheets. "Worksheet_Activate" runs the "UpdateTOC" macro every time that the TOC sheet is selected. "Worksheet_SelectionChange" runs every time that the selected cell on the TOC sheet is changed (i.e. by clicking or moving with the cursor keys). Where the active cell is in the first column (A) and the cell isn't blank, it will try to go to the sheet named in that cell. If no such sheet can be found (possible if you've manually typed something into the TOC sheet since the last update, it will display an error message and update the TOC. If the sheet exists but is hidden, it will display a message asking you to make another selection.

 

[Trevor_S]

I've noticed that the screen image in my last post didn't upload - so have linked to it here:
https://drive.google.com/open?id=0B_dlXxlE0XVBRWtmQTVDWmVEa1U

Also, in case it helps - some test files to demonstrate how it works and where you need to put the macros.
Include the hidden sheets in the TOC:
https://drive.google.com/open?id=0B_dlXxlE0XVBcURGUlJCN3RfdDQ
Exclude the hidden sheets from the TOC:
https://drive.google.com/open?id=0B_dlXxlE0XVBSTZVckZsMzZBTkU

 

[MrExcenTric]

Hi Trevor,

thx for your enthusiasm & assistance.
Was offline for the day, so not able to get back before.
So I hope the following additional input you asked, is still in line whith what you came up with.

No, it turns out to be perfectly possible to create a ToC with a combination of "classical" formulas.
If you don't count copy & pasting formulas, no manual entering is needed.
The idea is to copy the following formula into, say, 50 cells (assuming that no Excel file will contain more sheets):
IFERROR(HYPERLINK(INDEX(GET.WORKBOOK(1),ROW()-6)&"!$A$1",SUBSTITUTE(INDEX(GET.WORKBOOK(1),ROW()),"["&$C$6&"]","")),"")

It uses some "old" Excel 4 functionality (XLM macros) which can still be made to work, but it is no longer documented.
In this particular case:
GET.WORKBOOK(1) returns the names of all sheets in the workbook as an horizontal array of text values. Names are returned as "[book]sheet".

Next, INDEX() is used to identify the sheet which corresponds to the current row.
And SUBSTITUTE() deletes "[]" and the workbook name it contains.

This result is turned into a hyperlink.

And finally, IFERROR makes sure that no errors are displayed if the files contains < 50 sheets (replacing the error message by a blank cell)

NB:
- "!$A$1" is needed because the link to another sheet must point to a cell in the sheet.
- the other values are used for "offsetting" the ToC within the active sheet.


So, if there is a way to extend the above to work around the hyperlink limitation (i.e. no reaction if linked sheet is hidden), I'm still curious to know how.
On the other hand, if the answer turns out to be "sorry, impossible!", I'm glad to observe that you've already provided everything needed in VBA, so it seems.
I'll definitely give your proposals a shot on short notice, thank you so much for your interest and kind assistance.


PS:
I hope this time my message will be displayed with line breaks, as I entered it. (I cannot edit my input to structure it again, which is much more comfortable to read)

MrE.

 

[Trevor_S]

That's clever, I didn't know about GET.WORKBOOK

I wonder if when you click a link for a hidden sheet, it is really going there, its just that you can't see it doing so (because its hidden...!)

So its really whether there's anything similar to this that only returns an array of visible worksheets. I'm guessing not, as I think that if there was, it would probably have been mentioned wherever you found this functionality.

Hopefully the macros will do what you need though!

 

[Trevor_S]

Looking at your formula, I'm guessing that you're using the first six rows of the TOC sheet for something else, that you wouldn't want overwritten? If so, change this row of the macro:

Sheets("TOC").UsedRange.ClearContents
TocRow = 1

to:

Sheets("TOC").Range("A7:A" & Sheets("TOC").UsedRange.Rows.Count).ClearContents
TocRow = 7

</pre>

 

[MrExcenTric]

Hi Trevor,

GET.WORKBOOK(1) seems to be the only built-in way by which Excel will return all sheet names of an active Workbook.
So yes, it comes in handy.

I agree with you that it makes sense indeed that the link isn't going anywhere if its destination is hidden.
However, it would be much nicer if the user were somehow notified that he musn't try to jump to that sheet.
By adding some marker, or even by having the link completely removed. Whatever.
But that's impossible as long as this hidden nature cannot somehow be retrieved.
(MS could have foreseen a warning at this point, right?)

The first six rows are sort of random, but correspond to some reserved space indeed (for a title or tutti quanti).
BTW, the second ROW() occurrence in my formula should also be ROW()-6.

MrE.

 

[Rick Rothstein]

So its really whether there's anything similar to this that only returns an array of visible worksheets.

You might find what I posted in my mini-blog post article here to be of interest...

A Neat "Go To Sheet" Selector

It is different than you originally asked for, but I still think you might like it. Rather than maintain a table of contents, it pops up a UserForm structured with a TextBox and ListBox where the ListBox is filled with all of the visible sheet names. You can scroll/select the sheet you want directly or begin typing the sheet name for the sheet you want and, as you type, the list will filter down to only those sheet names that start with the letters you typed so far. You can stop typing any time and select the sheet from the then displayed sheet names. I propose making this an Excel Template so that you can use it any time you want.