Hyperlink to Hidden Sheet

[IDAK]

Hello all,

Can someone help me out? I am still a noob to the VBA coding but I am having issues with getting my Hyperlinks to work to a hidden sheet.

My main sheet "Project Checklist" has four hyperlinks on it directing it to a Hidden sheets one of those sheets is the " Alarm Check Process" sheet. When the sheet is hidden the hyperlinks no longer work.

My "Alarm Check Process" sheet does have a simple code on it to hide the page once you navigate away from it and it works. This code is as follows:

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub


I have tried using this code below on the "Project Checklist" sheet but I can not seem to get it to work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub


Can someone help me and tell me what I might be doing wrong?

Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel
How about

ShtName = Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")

 

[IDAK]

I have tried replacing line 2 with what you provided and clicked save and closed visual basic and it still does not work.

When I click on Debug and run to cursor at the end of this entire code it does nothing.

I have this directly on the worksheet in visual basic.

Not really sure why I can not get this to work.

 

[Fluff]

What error do you get & what line is highlighted?

 

[IDAK]

That is the issue no line is highlighted and no error is provided.

I my other code for the hidden sheet to deactivate gave me a highlighted line and an error when i had something wrong with it.

 

[Fluff]

In that case, in what way didn't it work?

 

[IDAK]

when I go to the sheet titled "Project checklist" and click on the hyperlink it does nothing now due to the "Alarm Check Process" sheet is hidden.

 

[Fluff]

Put a breakpoint on this line

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub

and then click the hyperlink, what happens?

 

[IDAK]

Ok I have entered a break point on that line.

When I try and step through the code to get it to highlight each line to run next I can not get it to function.

My computer just chimes and that is it. No error, no highlighted lines or anything.

 

[Fluff]

You need to click the hyperlink, after setting the breakpoint.