Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

**ThiaJay** · 08-07-2020, 04:54 AM

Hello there, I have too many tabs/worksheets to scroll effectively through so I wanted to create a contents page with links in rows instead. This also enables me to add descriptions of what is in each of the worksheets.

It works fine if I have the worksheets visible, but I want to stop there being any confusion so I want to hide all the worksheet tabs linked in the contents worksheet, but still being able to access the worksheets.

Could someone please confirm if this is possible without coding or does this need to be done as a macro/code?

**ChemistB** · 08-07-2020, 10:35 AM

This would require VBA coding.
Do you know the following tips for maneuvering through sheet tabs?

1. CNTRL PageUP or PageDwn to move from sheet to sheet
2. Cntrl + Click on scroll arrows (lower right) to move the tabs to show last (or first) sheet
3. Right click on the scroll arrows to bring up the list of all sheets. You can then select which sheet to travel to.

**ThiaJay** · 08-07-2020, 01:55 PM

Originally Posted by ChemistB

This would require VBA coding.
Do you know the following tips for maneuvering through sheet tabs?

1. CNTRL PageUP or PageDwn to move from sheet to sheet
2. Cntrl + Click on scroll arrows (lower right) to move the tabs to show last (or first) sheet
3. Right click on the scroll arrows to bring up the list of all sheets. You can then select which sheet to travel to.

Thanks for your reply. I didn't, but then again that won't make it easier for others who will not remember that nor will it really help them know what is on each page. The contents page is great because it contains descriptions of what is contained too.

Does anyone know what code would be involved to be able to view a hidden worksheet called "Local Contacts"?

**ThiaJay** · 08-11-2020, 05:26 AM

Just checking that this has been seen.

**Andy Pope** · 08-11-2020, 10:31 AM

Add code to Thisworkbook module.

Please Login or Register  to view this content.

On the Table of contents sheet you would need to insert hyperlinks, not HYPERLINK formula.

**ThiaJay** · 08-12-2020, 04:40 AM

Thanks Andy, I did that originally (inserting hyperlinks via the menu rather than formula) but it's not working.

How would we ensure that Sheet1 (Called 'Search') and Sheet8 (Called 'Contents') were always visible?

Did you hide all the other sheets once you inserted the code into 'ThisWorkbook'?

Please Login or Register  to view this content.

Debug doesn't like this line:

Please Login or Register  to view this content.

**ThiaJay** · 08-12-2020, 05:09 AM

Ah, thank you.

The problem was the order, I missed that.

Is there a way to make this work by making the hidden sheets hidden just by changing to the "Search" or "Contents" tab rather than clicking on a hyperlink? I don't have space in the top cells.

Please Login or Register  to view this content.

**Andy Pope** · 08-12-2020, 06:04 AM

Stick code in the worksheet activate event to hide other sheets.

**ThiaJay** · 08-12-2020, 07:05 AM

Thanks Andy.

Is there a way to make this work by making the hidden sheets hidden just by changing to the "Search" or "Contents" tab rather than clicking on a hyperlink? I don't have space in the top cells.

Also, how would we ensure that Sheet1 (Called 'Search') and Sheet8 (Called 'Contents') were always visible?

**Andy Pope** · 08-12-2020, 07:10 AM

Yes I just suggested adding code to the activate events of the search or contents sheet.

Add code to test whether the sheet tab has a name that should be kept unhidden

As always it would make things a lot clear to us if you posted example workbook. You keep on referencing things we have not had sight of

**ThiaJay** · 08-12-2020, 07:40 AM

Sorry Andy, here you go.

**Andy Pope** · 08-13-2020, 03:53 AM

See Thisworkbook SheetActivate event for the code.

**ThiaJay** · 08-13-2020, 04:22 AM

That's totally awesome, thank you. The only thing that isn't working is avoiding searching through the contents page with the code updated?

**Andy Pope** · 08-13-2020, 04:28 AM

Your code already ignores the Search sheet so you should be able to add code to ignore the contents sheet.

**ThiaJay** · 08-13-2020, 05:05 AM

I tried to quote code but the website firewall said I was trying to inject code, so I will post it as an image.

Gb5h26Q.png

**Andy Pope** · 08-13-2020, 05:20 AM

I meant you have code in your search routine that excludes

Please Login or Register  to view this content.

**ThiaJay** · 08-13-2020, 05:24 AM

If I insert an extra line, it breaks:

Please Login or Register  to view this content.

**Andy Pope** · 08-13-2020, 05:27 AM

That is because that is not how you add additional tests to the IF statement.

Please Login or Register  to view this content.

**ThiaJay** · 08-13-2020, 06:18 AM

Thank you Andy, I did not realise. That's really helpful and gives me a better insight.

**ThiaJay** · 08-13-2020, 07:32 AM

I've noticed a problem now. If a hyperlink is introduced that is external and clicked, such as for an email address or external workbook, the code in ThisWorkbook isn't happy.

Please Login or Register  to view this content.

Specifically

Please Login or Register  to view this content.

Run-time error '1004':

Method 'Range' of object '_Global' failed.

**Andy Pope** · 08-13-2020, 07:35 AM

Then add code to check subaddress or use error trapping

**ThiaJay** · 08-14-2020, 03:32 AM

Hello Andy, thank you for your reply.

I'm not sure how/what I would need to input to check the subaddress.

**Andy Pope** · 08-14-2020, 04:11 AM

Did you write any of that code, you seem to be struggling with VBA coding? I only ask as it would be helpful to those trying to help you if we understood your level of VBA knowledge.

Try this

Please Login or Register  to view this content.

**ThiaJay** · 08-14-2020, 04:56 AM

Thank you Andy. It's obvious now that you overestimated my abilities, I have either manipulated code or added it based on your support and that or someone on the other thread.

What you have said does work.

I've noticed that the hyperlinks that used to work on the search page for any results that come up and take the user to the relevant worksheet and cell don't work now. It's likely this is to do with the relevant worksheet being hidden. I'm not sure what the difference is between the links on the contents page and those produced by the code on the search page?

**Andy Pope** · 08-14-2020, 04:57 AM

Attach a workbook with an example of non worksheet link, it maybe different to the test one I tried and based the code on

**ThiaJay** · 08-14-2020, 05:29 AM

But the non worksheet link works as you expected. It's the links generated by the search code on the Search tab that don't work since all the modifications have been made to hide worksheets (and those are links to cells on hidden worksheets within the same workbook).

**Andy Pope** · 08-14-2020, 05:36 AM

All I can say then is works for me. Without seeing exactly what you are dealing with I can not help.

**ThiaJay** · 08-14-2020, 06:02 AM

Have you tried searching on the Search tab for "test" and then clicking on the links in the left column?

**Andy Pope** · 08-14-2020, 06:16 AM

The followhyperlink checks the sheet name and was only working for Contents.
If you also want the Search tab to work you need to test that sheet name.

Please Login or Register  to view this content.

**ThiaJay** · 08-14-2020, 07:31 AM

Thank you, that does work

Please Login or Register  to view this content.

**ThiaJay** · 08-14-2020, 12:46 PM

It's working well, even if the user saves the workbook on one of the hidden worksheets, when the worksheet is opened up again it hides that sheet and the active sheet is either Search or Contents. How do I force the view to revert to Search only? Even if the workbook is saved on the Contents page please?

**Andy Pope** · 08-15-2020, 04:08 AM

add code to the Thisworkbook Workbook_Open event to activate the required sheet

**ThiaJay** · 08-17-2020, 05:44 AM

Thanks Andy.

Please Login or Register  to view this content.

Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Re: Creating a contents page with hyperlinks that take the user to hidden worksheet tabs

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] trying to access the excel sheets/Tabs in the hidden/very hidden mode through hyperlinks

[SOLVED] How to use macros to open hidden tabs from a master worksheet?

[SOLVED] Creating a List of Worksheet Tabs in a workbook as a "Contents Page"

Bookmarks

Bookmarks

Posting Permissions