Help to hide / open hyperlinked sheets using button/macro

[KELLIS]

Hi I have a spreadsheet to monitor work performance, targets, projects. I have used form buttons/ macros to open sheets from a main menu. I have used macros/buttons to navigate backwards towards the main menu that will show / hide the sheets, I do not wish all the sheets to be visible. I then decided this was a bit fussy to be making buttons and macros for everything and decided on one page to use hyperlinks within text boxes to open new sheets (easier, less fussy). The problem I have is I still want to navigate backwards to the main menu how can I do this with a hyperlink sheets. If I use a macro/button to navigate backwards making the open sheet hide itself it hides completely and the hyperlink can no longer be opened.

Any suggestions. I could go back and change all the hyper links to macro/buttons but it is easier with the hyperlinks.

Any suggestions

[AlphaFrog]

I'm not sure I follow exactly what you are doing.

This would unhide a sheet when you click its hyperlink on the Main Menu worksheet. Put this code in the Main Menu worksheet's code module.

Please Login or Register  to view this content.

[KELLIS]

Thanks AlphaFrog I think you do understand what I am looking for.

The main menu is a page of buttons which open other sheets within the workbook i.e "Open Daily Stats", "Open Projects", "Open TD Info". The Open TD Info page opens a sheet with many more hyperlinks leading to other sheets. On the other main sheets "Daily Stats" etc I have used buttons and macros to make the tabs visible/invisible. However on the TD Info sheet there is 20 or so sheets opened by the hyperlinks which I do not want visible until needed. I tried a button with macro to "Return to TD Info" which opens the "TD Info" sheet and hides the tab the button is on. This basic macro works to hide the sheet but then when I click on the hyperlink again to open the hidden sheet does not open.
Would the code above help with that and where would I place the code? Would I add the code into the macro that opens "TD Info"

Thanks in advance, sorry i'm not too good at explaining.

[AlphaFrog]

The code is triggered when a hyperlink is clicked. It goes in the worksheet's code module that has the hyperlink. So if I follow you correctly, the TD Info sheet has the hyperlinks. So right-click on the TD Info sheet tab and select View Code from the pop-up context menu. Then paste the code in the worksheet's code module.

It can go in any of your worksheet code modules that have hyperlinks where you want to unhide the destination worksheet. And it can go in multiple worksheet code modules at the same time if needed.

The code basically does the same thing as a hyperlink. It gets the link address from the clicked hyperlink. Then unhides the destination sheet and follows the link.

[KELLIS]

Hi AlphaFrog

Not sure what I'm doing wrong I have copied the code directly into the TD Info sheet. Once I have returned to the TD Info sheet and the Apps & Guides sheet is hidden I then try to open the Apps & Guides sheet again by clicking on the link and it doesn't open. Do I need a control to run the macro. Sorry i'm not understanding.

Thanks

[AlphaFrog]

The macro is suppose to run automatically when you click a hyperlink that was created from the menu Insert\Hyperlink.
Did you create the hyperlinks by using Insert\Hyperlink from the menu or by using the HYPERLINK function in a formula?

Debugging VBA Code
Put a line break in the code to determine if it is being triggered.

Comment out the On Error line for now and let me know if it errors. If yes, what line?

[KELLIS]

Hi Again

I created the links on the sheet within a text box by insert hyperlink. I cannot get the code to run or test as when I hit run the macro box appears. I really do not know much more than very basic code.
I have inserted the code into sheet 17 TD Info in the excel objects in VBAProject . My hyperlink to the Apps & guides sheet works fine when the sheet tab is open but as soon as I hide the tab the link no longer works no errors. I cannot see where it goes wrong as I do not know how to test the macro.
I am sorry I can understand if you give up on me.

[AlphaFrog]

I created the links on the sheet within a text box by insert hyperlink.

By "text box", do you mean a cell on the worksheet or a TextBox object from the Control Toolbox toolbar or the Drawing toolbar? I understand inserting a hyperlink into a worksheet cell, but not into a "text box".

It seems you pasted the code in the proper place.

As a test, use this code and tell me if a message box pops up when you clikck the link.

Please Login or Register  to view this content.

[KELLIS]

Hi
I have not tried the above yet but tried the code on the next sheet, From Apps & guides there is a link to a sheet called Evacuation Guide when this is hidden the code opens it again no problem it seems the problem is with the original TD Info sheet!!

I use a text box from drawings and insert hyperlink. I shall try the above now.

[KELLIS]

Hi

No message appears. Maybe it is just too late for my little brain. Thanks for all your help I'll get all the other sheets working and then return to the TD Info Sheet.

[AlphaFrog]

I use a text box from drawings and insert hyperlink.

Do the other sheets that work have a "Text box" hyperlink or a cell hyperlink?

I cannot make a "Text Box" hyperlink. Why did you do that instead of a hyperlink in a cell?

As a test, can you insert a cell hyperlink on TD Info sheet?

[KELLIS]

Oh Dear me I am so sorry for wasting your time, although I have definitely learned!! The message is 'triggered'. The TD sheet had hyperlinks in a text box which clearly work however not with hidden sheets and code, yet the sheets after that were hyperlinks within cells with borders which as you have proved work perfectly. I have always used the text box because it looked better.

Thank you so much. One last question in your opinion would I have been better using all hyperlinks to navigate the workbook. What I started using was form buttons and codes to navigate back to the menus closing the sheets as needed. Then on the sheet that I have the majority of links on I used hyperlinks. I now suspect I have went to a lot of bother with the codes (different one for all the sheets in order to hide / unhide) when I did not need to. However it has certainly given me practice.