Hyperlink to Hidden Sheet

IDAK

New Member
Joined
Mar 5, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
How about
VBA Code:
ShtName = Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")
 

IDAK

New Member
Joined
Mar 5, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • 2020-03-05 08_44_30-Microsoft Visual Basic for Applications - T23 - Customer_Project Checklist...png
    2020-03-05 08_44_30-Microsoft Visual Basic for Applications - T23 - Customer_Project Checklist...png
    52.2 KB · Views: 10

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
What error do you get & what line is highlighted?
 

IDAK

New Member
Joined
Mar 5, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
In that case, in what way didn't it work?
 

IDAK

New Member
Joined
Mar 5, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Put a breakpoint on this line
Rich (BB code):
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

New Member
Joined
Mar 5, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
You need to click the hyperlink, after setting the breakpoint.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,244
Messages
5,595,032
Members
413,962
Latest member
PedroGomez9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top