Hyperlink to hidden sheet using a cell with the =Hyperlink formula

Hi,

Hoping that someone can help with a hyperlink / VBA problem that's driving me crazy!?

Basically, I have a workbook with a front sheet which contains a column of hyperlinks to other sheets within the same workbook. Each of these hyperlinks is built using the =HYPERLINK formula rather than a right-click>Link type hyperlink. I had to build the hyperlinks using the formula method as various preceding columns are concatenated in order to build the name of the sheet which requires linking to.

So, with all sheets in the workbook unhidden, the formulated hyperlinks work perfectly. I can use them to jump to the relevant sheets with no problems. However, to tidy up the workbook, I want to be able to hide all of the individual sheets except for the front sheet - and then when I click on a hyperlink, only the relevant sheet would be unhidden for editing & then re-hidden once a 'Back' button is pressed. I just can't get the hyperlinks to work once the individual sheets are hidden - clicking the links does nothing, and I can't figure out why or how to fix it?

I've done something similar in the past using the sheet code below - but this one only seems to work using 'normal' hyperlinks. I'm guessing it's not working for me now because i'm using the =HYPERLINK formula? If so, anyone any ideas how I can get around this and achieve what I'm trying to do? Apologies if this has been answered before, I had a search but couldn't find anything.

Cheers,
Paul

Please Login or Register  to view this content.

This dialogue might help you. https://www.excelforum.com/excel-gen...orksheets.html

Pete

Thanks Pete. I recognise that thread, I think I may have already come across it whilst trying to find an answer, but had no joy.

The codes on that page seem to all involve the Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) command line, but I think my issue is because I'm using the =Hyperlink formula, rather than inserting links, I don't think VBA is treating my hyperlinks as hyperlinks and so not following them - if that makes sense?! I'm a bit of a VBA novice, so not 100% sure if i'm talking nonsense here or not!

I'm still no closer to cracking this one...

Any luck with this? I'm working on a project just like this, wondering if you came up with a solution?