Problem with hide/unhide VBA and hyperlinks

[DominiX1]

Hi, hope you can help

I have a workbook with hundreds of spreads. However I have used a macro to hide them all and unhide them by using a hyperlink for all of the spread sheets. I.e. Index sheet with all the names of different sheets and by clicking on a specific sheets name it opens and by clicking back on Index sheet it hides. It works perfectly fine, however I would like to add another hyperlink in the index sheet, this time that would get me to a website. The hyperlink works and it redirects me to the webpage, but it comes up with a Runtime error '9' every time I try to open the hyperlinked web page

The code I use is below. Even thou it works, I would like not to get an error message once opening a hyperlink for a webpage, as this spread would be widely used once completed.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.ScreenUpdating = False
Dim strLinkSheet As String
If InStr(Target.Parent, "!") > 0 Then
strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Else
strLinkSheet = Target.Parent
End If
Sheets(strLinkSheet).Visible = True
Sheets(strLinkSheet).Select
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Activate()
On Error Resume Next
Sheets(ActiveCell.Value2).Visible = False
End Sub

I hope to find a solution for the problem, please advise..

 

[offthelip]

looking at your code it would seem that if the target is not sheet name which you detect by looking at the "!" then you set strlinksheet to the target ( which woulkd be a weblink ) and then you do this:

[COLOR=#333333]strLinkSheet = Target.Parent[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Sheets(strLinkSheet).Visible = True[/COLOR]
[COLOR=#333333]Sheets(strLinkSheet).Select[/COLOR]

which doesn't make sense at all because you probably don't have a sheet called the same name as your weblink.
so I suggest moving the endif as below:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.ScreenUpdating = False
Dim strLinkSheet As String
If InStr(Target.Parent, "!") > 0 Then
strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Sheets(strLinkSheet).Visible = True
Sheets(strLinkSheet).Select



End If
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Activate()
On Error Resume Next
Sheets(ActiveCell.Value2).Visible = False
End Sub

 

[DominiX1]

Thank you for the advise offthelip however now it just doesn't open the hidden sheets.

The point of the original Macro was to open a hidden sheet and then hide it once moving back to the Parent Sheet (Index sheet)
As it is hidden, using the Macro you suggested has opened the Webpage hyperlink no problem (thank you) but it doesn't open the hidden spread sheets.

Any advise on how to make it open the hidden sheet and then hide it as the original macro would, while remaining the functionality to open webpage hyperlinks?

Many thanks

 

[offthelip]

if it is not opening the hidden sheets then you need to change the if statement to correctly identify when the "target" is pointing at hidden sheet.
Use the debug and point a breakpoint at the if statement and see what it looks like when you select a hiden sheet and then test for that:

If InStr(Target.Parent, "!") > 0 Then

 

[DominiX1]

if it is not opening the hidden sheets then you need to change the if statement to correctly identify when the "target" is pointing at hidden sheet.
Use the debug and point a breakpoint at the if statement and see what it looks like when you select a hiden sheet and then test for that:

If InStr(Target.Parent, "!") > 0 Then

Dear offthelip

As I am an absolute starter with VBA, it might sound silly but I am unable to find a mistake. Even by bringing a breakpoint in the VBA code it simply stops from functioning and I don't know where to go from there.

I know for some it might look like I'm asking to be spoon-fed, but honestly speaking - I don't know half of the things across excel and am hoping some of the gurus of this forum may be of assistance.

Many thanks

 

[DominiX1]

Hi all,

Sorry to raise this up, but still can't find the solution for problem.

Please advise as the above suggested code didn't work and I can't work out how to change the target (VBA starter)

Thanks for the help in advance

 

[John_w]

Try this (put the code in the sheet module for the 'index' sheet):

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim strLinkSheet As String
    Application.ScreenUpdating = False
    If InStr(Target.Parent, "!") > 0 Then
        strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
        Sheets(strLinkSheet).Visible = True
        Sheets(strLinkSheet).Select
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    Dim strLinkSheet As String
    'On Error Resume Next
    If InStr(ActiveCell.Value2, "!") > 0 Then
        strLinkSheet = Left(ActiveCell.Value2, InStr(1, ActiveCell.Value2, "!") - 1)
        Sheets(strLinkSheet).Visible = False
    End If
End Sub

 

[DominiX1]

Try this (put the code in the sheet module for the 'index' sheet):

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim strLinkSheet As String
    Application.ScreenUpdating = False
    If InStr(Target.Parent, "!") > 0 Then
        strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
        Sheets(strLinkSheet).Visible = True
        Sheets(strLinkSheet).Select
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    Dim strLinkSheet As String
    'On Error Resume Next
    If InStr(ActiveCell.Value2, "!") > 0 Then
        strLinkSheet = Left(ActiveCell.Value2, InStr(1, ActiveCell.Value2, "!") - 1)
        Sheets(strLinkSheet).Visible = False
    End If
End Sub

No, unfortunately the above will only open the web link, but not the hidden worksheet hyperlink..

Have been doing some searching across the web, but still can't find a solution.

Maybe, instead of fixing the problem somebody could suggest a CODE to run the required functions

I use a Macro activated button to hide/unhide sheets all of the Sheets apart from the "Summary" sheet (index sheet)

 Sub Button1_Click()For Each ws In Sheets: ws.Visible = True: Next
Application.Goto Sheets("Summary").Range("A1"), True
End Sub

Sub Button2_Click()
For Each ws In Sheets
If ws.Name <> "Summary" Then
ws.Visible = xlSheetHidden
End If
Next ws
End Sub

Now what I'd like is when I've used the Macro to hide them all, I have all of the sheets' names with a hyperlink in my "Summary" page i.e. Sheet1, Sheet2, Sheet3 etc. Once I click a hyperlink - it opens the sheet and when I return back to "Summary" it hides it again.

In addition to the above, I would like to add a web page hyperlink in my "Summary" page which when clicked would redirect to a web page link.

My original code works on both, but it comes with a Runtime error 9

I will appreciate any help with this

 

[John_w]

What is displayed in the cell for your hyperlinks to cells in the other sheets? My first code was based on your OP and assumes the displayed link contains "!", e.g. the displayed hyperlink is "Sheet2!A1".

The following code looks at the underlying hyperlink instead of the displayed text. The underlying hyperlink always contains "!" for a link to a 'place in this document'.

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim strLinkSheet As String
    Application.ScreenUpdating = False
    If InStr(Target.SubAddress, "!") > 0 Then
        strLinkSheet = Split(Target.SubAddress, "!")(0)
        Sheets(strLinkSheet).Visible = True
        Sheets(strLinkSheet).Select
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    Dim strLinkSheet As String
    'On Error Resume Next
    If ActiveCell.Hyperlinks.Count > 0 Then
        If InStr(ActiveCell.Hyperlinks(1).SubAddress, "!") > 0 Then
            strLinkSheet = Split(ActiveCell.Hyperlinks(1).SubAddress, "!")(0)
            Sheets(strLinkSheet).Visible = False
        End If
    End If
End Sub

 

[DominiX1]

What is displayed in the cell for your hyperlinks to cells in the other sheets? My first code was based on your OP and assumes the displayed link contains "!", e.g. the displayed hyperlink is "Sheet2!A1".

The following code looks at the underlying hyperlink instead of the displayed text. The underlying hyperlink always contains "!" for a link to a 'place in this document'.

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim strLinkSheet As String
    Application.ScreenUpdating = False
    If InStr(Target.SubAddress, "!") > 0 Then
        strLinkSheet = Split(Target.SubAddress, "!")(0)
        Sheets(strLinkSheet).Visible = True
        Sheets(strLinkSheet).Select
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    Dim strLinkSheet As String
    'On Error Resume Next
    If ActiveCell.Hyperlinks.Count > 0 Then
        If InStr(ActiveCell.Hyperlinks(1).SubAddress, "!") > 0 Then
            strLinkSheet = Split(ActiveCell.Hyperlinks(1).SubAddress, "!")(0)
            Sheets(strLinkSheet).Visible = False
        End If
    End If
End Sub

Dear John_w

In the cells for the hyperlink I literally have the name of the sheet itself.

For example In A Column I'd have:

Sheet1
Sheet2
Sheet3

and that is a true reflection of what's the names of the sheet.

I did find it that the code would not work if the text in the cell containing hyperlink would change (not match to sheet's name) OR if the targeted sheet's name would change.

For the above code - It comes out with a runtime error 9 when opening sheet's hyperlink. Web hyperlink works perfectly fine, and it's highlighting this part of your code when I go Debug:

Sheets(strLinkSheet).Visible = True

I know that the solution is right behind the corner. Let me know if you'd like to see the part of the workbook I'm using so maybe that would help... Or there's just not enough information in my explanation.

I'm sure one of the excel gurus will be able to help.

Many thanks