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..

 

[DominiX1]

I am slowly giving up on the original solution as I have played with the code for the last 2 days, but it's either one or the other that don't work.

I am open to suggestions as to how to make my workbook to be able to follow hyperlinks for sheets as I am utterly disgusted by the idea of having several hundred open spread sheets AND to be able to open web hyperlinks in the same time.

Please be advised that none of the above codes have given the required results and the closest to succession (hyperlinks actually working) is the original, but it comes with a Runtime Error "9" when web hyperlink is opened.

I am desperately hoping for some help.

Many thanks

 

[offthelip]

I haven't been following this thread entirely but it seems to me that the problem you have is in detecting whether your hyperlink is a web address or worksheet. One way which you could guarantee this is instead of looking at the hyperlink itself look at the worksheet address where it is located. Then the simple way is to have the worksheet links in one column and the web links in another column. If you want them all in one column you could create a "mapping" array to record which are which and check against . To get the cell where the hyperlink is this works:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)Set hr = Target.Range


colno = hr.Column
rowno = hr.Row


MsgBox (colno & " " & rowno)


End Sub

 

[DominiX1]

I haven't been following this thread entirely but it seems to me that the problem you have is in detecting whether your hyperlink is a web address or worksheet. One way which you could guarantee this is instead of looking at the hyperlink itself look at the worksheet address where it is located. Then the simple way is to have the worksheet links in one column and the web links in another column. If you want them all in one column you could create a "mapping" array to record which are which and check against . To get the cell where the hyperlink is this works:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)Set hr = Target.Range


colno = hr.Column
rowno = hr.Row


MsgBox (colno & " " & rowno)


End Sub

I think I lost you there. The hyperlinks for the worksheets would be in one column (let's say Column A) and the hyperlinks for web pages would be in a different column (let's say Column B) and this would be the way it would be, there wouldn't be any occasion where both would be mixed up together.

Would that form the part of the original code or is that a completely different sub as I've not done any mapping on worksheets beforehand, sorry.

I do appreciate all of the attempts to sort my issue out and apologise for my dim-wittedness.

This would roughly be what the Index sheet looks like (or the part of it I am facing difficulties with)

Name	Webpage
Andrew	https://www.linkedin.com/in/Andrew...
Tyler	https://www.linkedin.com/in/Tyler...

<tbody>
</tbody>

Where Andrew and Tyler are hyperlinked to separate spread sheets named 'Andrew' and 'Tyler' and clicking on those, it would redirect you to that spread.

And obviously clicking the webpage hyperlink would open the hyperlink in the browser.


Many thanks

 

[DominiX1]

Bumping this up for more attention.

I am trying out different ways of not using the web hyperlinks in the same index sheet as page hyperlinks are in, but that's when I lose the point of hiding the sheets in the first place.

Any and all help is appreciated on this matter.

Many thanks

 

[offthelip]

the way i am think ing of solving the detection problem is using code like this.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'assuming that hyperlinks to spreadsheets are in col A
' and hyperlinks to websites are in Col B
Set hr = Target.Range
colno = hr.Column
If colno = 1 Then
' put your code that opens spreadsheets in here
End If
If colno = 2 Then
 'put your code that opens websites in here
End If
End Sub

 

[DominiX1]

the way i am think ing of solving the detection problem is using code like this.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'assuming that hyperlinks to spreadsheets are in col A
' and hyperlinks to websites are in Col B
Set hr = Target.Range
colno = hr.Column
If colno = 1 Then
' put your code that opens spreadsheets in here
End If
If colno = 2 Then
 'put your code that opens websites in here
End If
End Sub

Dear offthelip,

I see your point, however I am not too gifted on amending the codes in terms to put 2 codes under 1 that would trigger on using links under set columns. So now I am down to the task of combining these under your suggested code:

This works for worksheet hyperlinks

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

and

This works for web hyperlinks

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

I feel that we are extremely close, I will still try to play around it, but any suggestions on how to combine the above 2 in to the one that offthelip suggested, will be greatly appreciated

Many thanks

 

[offthelip]

this should do it for you:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strLinkSheet As String


'assuming that hyperlinks to spreadsheets are in col A
' and hyperlinks to websites are in Col B
Set hr = Target.Range
colno = hr.Column
If colno = 1 Then
    ' put your code that opens spreadsheets in here
     Application.ScreenUpdating = False
     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 If
If colno = 2 Then
 'put your code that opens websites in here
        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 If
End Sub


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

Actually I think that some of your code is unnecessary but I stick be the old adage if it works don't touch it.

 

[DominiX1]

this should do it for you:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strLinkSheet As String


'assuming that hyperlinks to spreadsheets are in col A
' and hyperlinks to websites are in Col B
Set hr = Target.Range
colno = hr.Column
If colno = 1 Then
    ' put your code that opens spreadsheets in here
     Application.ScreenUpdating = False
     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 If
If colno = 2 Then
 'put your code that opens websites in here
        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 If
End Sub


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

Actually I think that some of your code is unnecessary but I stick be the old adage if it works don't touch it.

I can't thank you enough, it works perfectly fine!

I don't mind if the code isn't clean or neat. As long as it gets the job done and doesn't get stuck - I am happy..

Many thanks

 

[offthelip]

I am pleased to be able to solve it after so many failed attempts