Hide Parent Page Hyperlink Excel

Question

I would like to hide the current page that the user is looking at when they click a hyperlink within excel that takes them to a different worksheet within the same workbook. I tried using the following code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  On Error GoTo Cleanup
  ActiveSheet.Visible = False
  Application.EnableEvents = False
  Target.Follow
Cleanup:
  Application.EnableEvents = True
End Sub

because I assumed the activesheet would be the sheet that the hyperlink is on and not the target sheet, however, ActiveSheet is the target sheet. Any suggestions on how to hide the partnet sheet?

Answer 1

This is going to sound odd, but you need to replace

ActiveSheet.Visible = False

with

Target.Parent.Parent.Visible = False

Why?

1. The "Target" is the Cell being linked to.
2. The Parent of that cell is the cell that is the source of hyperlink
3. The parent of that cell is the worksheet

Answer 2

The best solution may be to create a list of sheets that can be visible when each sheet is active, then instead of using the FollowHyperlink event, use the Worksheet_Activate event to hide/unhide the necessary sheets.