View/Close Hyperlinked Hidden Worksheet

[MGumbrell]

I have a workbook that contains a worksheet (Index Page) with 53 hyperlinks to the related 53 worksheets that are hidden.

What I would like to do

01 - Maintain the 53 worksheets as hidden unless the relevant hyperlink is clicked. Showing the page requested by clicking the hyperlink.

02 - Once the "Back" Hyperlink on the sheet requested is clicked or the user clicks a visible TAB that the requested worksheet closes and goes back to being hidden.

I would be grateful if you could show me the code for this and where it should go i.e Class Module etc,

Regards, Matt

 

[shades]

Yes, this can be done. I have some code that does this. I will try to find it tomorrow (unless someone posts code earlier).
________
Buy Silver Surfer

 

[MGumbrell]

Thank you Shades

Look forward to hearing from you.

Matt

 

[HaHoBe]

Hi, Matt,

code goes into ThisWorkbook, name of visible worksheet has to be adapted if needed:

Option Explicit

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Sh.Name = "TOC" Then
  With Sheets(Left(Target.Name, InStr(1, Target.Name, "!") - 1))
    .Visible = True
    .Activate
  End With
  Application.Goto reference:=Range(Mid(Target.Name, InStr(1, Target.Name, "!") + 1, Len(Target.Name)))
Else
  Sh.Visible = xlSheetVeryHidden
  Sheets("TOC").Activate
  Application.Goto reference:=Range(Mid(Target.Name, InStr(1, Target.Name, "!") + 1, Len(Target.Name)))
End If
End Sub

Ciao,
Holger

 

[MGumbrell]

Thank You HaHoBe

Just what I was after, I will have a look through the code to see what has been written and how it works.

Thanks, Matt

 

[MGumbrell]

HAHoBe

Can you help please, I am getting a runtime error 9 on this line of code. I am unsure what this line of code does so I am unable to work out where it isn't working.

Is it looking for the name of the worksheet that is to the left of the "!" in the hyperlink? I have placed a "!" as the last character of the hyperlink title but I still get the runtime error.

With Sheets(Left(Target.Name, InStr(1, Target.Name, "!") - 1))

Thank you, Matt

 

[HaHoBe]

Hi, Matt,

you´re right with what you guess - if I put in a hyperlink it always reads something like SheetName!Range (Sheets1!A10). And it will only work with a link like that.

If you altered anything in the code or the hyperlinks it´s hard to tell for me why th eruntime error occurrs (okay - there got to be something wrong ) - I would rather prefer to look at an example instead of starting to guess.

Ciao,
Holger

 

[MGumbrell]

Fully understand Holger

please find attached file. All the worksheets are hidden as required leaving the index page. I tried to rename the "A1" ref but I still get the runtime error.

Look forward to finding out where the problem(s) are.

Thanks, Matt

Please note that I have had to substantially reduce the size of my files and that there are 53 pages of the selection posted.

 

[HaHoBe]

Hi, Matt,

don´t know but looking at your sheetnames the string was like "'A1'!A1" which is a little different to what I had when I wrote the code. The following amendments work fine for me (code only in ThisWorkbook):

Option Explicit

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Sh.Name = "Index Hyperlink" Then
  With Sheets(Mid(Target.Name, 2, InStr(1, Target.Name, "!") - 3))
    .Visible = True
    .Activate
  End With
  Application.Goto reference:=Range("A1")
Else
  Sh.Visible = xlSheetVeryHidden
  Sheets("Index Hyperlink").Activate
  Application.Goto reference:=Range(Mid(Target.Name, InStr(1, Target.Name, "!") + 1, Len(Target.Name)))
End If
End Sub

I stripped sheets 5 to 7...

Ciao,
Holger

 

[MGumbrell]

Thank you Holger.

Matt

 

[Xeroku]

I just want to confirm is this a code that can be ran in Excel? If so where do you place it and will it run after I click my hyperlinks? I have a worksheet that is in need of the same thing and I have pasted the code in but nothing is happening. Thank you for your assistance in advance.

 

[Xeroku]

still need help

Ok this time I included the current test excel file so you can see what I am working on. Thank you.

 

[HaHoBe]

Hi, Xeroku,

in your case the sheet name which is extracted doesn´t fit the real names. I´ll need to do some further testing on the code (which I can only do this evening) but maybe the following altering will do the job:

...
  With Sheets(Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1))
...

Ciao,
Holger

 

[HaHoBe]

Hi, Xeroku,

the links inside the worksheet should show the propper name for the corresponding sheet (that would help for those using the workbook without VBA).

For the better use of the links I would recommend to use the following code (like stated above - the names for the hyperlinks shoudl read properly inside the sheets with this code):

Option Explicit

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim strShNames As String
strShNames = "Cheryl, Kassandra, Adryan, Jason, Roxann"
If InStr(1, strShNames, Sh.Name) > 0 Then
  With Sheets(Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1))
    .Visible = True
    .Activate
  End With
  Application.Goto reference:=Range("A1")
Else
  Sh.Visible = xlSheetVeryHidden
  Sheets(Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1)).Activate
  Application.Goto reference:=Range(Mid(Target.Name, InStr(1, Target.Name, "!") + 1, Len(Target.Name)))
End If
End Sub

Build a string with the names of the sheets to have those sheets on hand which should be visible (at least that is what I suggest them to be), then decide whether to make the hyperlinked sheet either visible or unvisible depending on jumping to the sheet or leaving it.

Ciao,
Holger

 

[Xeroku]

This works great only they all point back to the first ARC tab. I was trying to get them to point to their own tabs. For example... have kasandra point to the ARC2 tab and Adryan point back to the ARC3 tab. While keep the other tabs hidden. I hope this one is clearer then my previous messages, I apologize if I was unclear and you have provided a lot, a lot of help thus far. Thank you.

 

[HaHoBe]

Hi, Xeroku,

the code presented here takes what is displayed in the sheets. And this is what I would suggest to use because to me it seems to be confusing to be shown to be going to to ARC1 but instead finding myself placed within ARC2

What you want is what is laid behind the links in the Hyperllink.Subaddress. So the code should be altered to look like

Option Explicit

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim strShNames As String
Dim ws As Worksheet
For Each ws In Worksheets
  If Left(ws.Name, 3) <> "ARC" And ws.Name <> "Summary" Then
    strShNames = strShNames & ws.Name & ", "
  End If
Next ws
If InStr(1, strShNames, Sh.Name) > 0 Then
  With Sheets(Mid(Target.[COLOR="RoyalBlue"]SubAddress[/COLOR], 1, InStr(1, Target.[COLOR="#4169e1"]SubAddress[/COLOR], "!") - 1))
    .Visible = True
    .Activate
  End With
  Application.Goto reference:=Range("A1")
Else
  Sh.Visible = xlSheetVeryHidden
  Sheets(Mid(Target.[COLOR="#4169e1"]SubAddress[/COLOR], 1, InStr(1, Target.[COLOR="#4169e1"]SubAddress[/COLOR], "!") - 1)).Activate
  Application.Goto reference:=Range(Mid(Target.[COLOR="#4169e1"]SubAddress[/COLOR], InStr(1, Target.[COLOR="#4169e1"]SubAddress[/COLOR], "!") + 1, Len(Target.[COLOR="#4169e1"]SubAddress[/COLOR])))
End If
End Sub

Ciao,
Holger

 

[Xeroku]

The only reason my supervisor wants it to go to the different ARC tabs is because they correspond to each associate. ^_^

 

[Xeroku]

Thank you soooooo much. It is exactly what my supervisor was looking for. I admit I just barely understand some of it. Is there something you would suggest that would help me understand the code better? Thank you again!!!

 

[HaHoBe]

Hi, Xeroku,

about the shown names of the hyperlinks in the worksheets: I would prefer to show the exact sheet´s name or use a more abstract term like "Details" instead of using ARC1!A1 (which to me should point to that worksheet and range).

About the code: if you record a macro you will learn what data will be inserted if a hyperlink is inserted. The first solution used what is to be seen in the sheet (that´s the reason why every link ended up on ARC1!A1) while the last solution used the propper address and thus followed the hyperlink no matter what was written in the worksheet.

The event is available since Excel2000 and reacts to all hyperlinks in the workbook. The names and number of worksheets may be different from the sample but as I learned the details will always be on sheets starting off with "ARC". So a loop may be used to fill a string with all the given names but leave the sheet Summary out of the string as well.
Next step is to check if a hyperlink is used if the name of the sheet is found in the string of names. If so make that sheet visible and go to that sheet. If the name of the sheet is not found (so the hyperlink must be on one of the "detail" sheets) that sheet is to be made invisible and the returning sheet is to be opened.

Please mind that there must always be at least one visible sheet in the workbook. In the given case the sheets starting the hyperlinks as well as the sheet Summary are always visible.

Ciao,
Holger

 

[Xeroku]

hmmm, ok I think that makes sense. So if my supervisor changes the names of the tabs or adds more it should still work? I greatly appreciate all of your assistance and providing me with this wonderful code and information. I can not thank you enough.