Hyperlink to hidden sheet with dynamic name

Question

Hyperlink to hidden sheet with dynamic name

Anonymous

Hi,

I am after some help with an issue i have come across. I have set up multiple sheets with dynamic names using what is in cell B1 of that sheet. These sheets are hidden and linked to from a master sheet.

The master sheet contains the below wherebang code however as i have now changed the hidden sheets to dynamic names they do not contain the "!" so the wherebang is never hit.

Is there a way i can use the HYPERLINK function so once clicked on the master sheet the hidden sheet is revealed and then the link from the hidden sheet back to the master sheet hides the page again.

I have put this together from different forums online so far so any help would be appreciated.

CODE FROM MASTER SHEET:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

LinkTo = Target.SubAddress

WhereBang = InStr(1, LinkTo, "!")

If WhereBang > 0 Then

MySheet = Left(LinkTo, WhereBang - 1)

Worksheets(MySheet).Visible = True

Worksheets(MySheet).Select

MyAddr = Mid(LinkTo, WhereBang + 1)

Worksheets(MySheet).Range(MyAddr).Select

End If

End Sub

CODE FROM HIDDEN SHEETS:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Worksheets("Combined P&L & Stats").Select

Target.Parent.Worksheet.Visible = False

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Set Target = Range("B1")

If Target = "" Then Exit Sub

On Error GoTo Badname

If Len(Target) > 31 Then

Target = Left(Target, 31)

End If

ActiveSheet.Name = Left(Target, 31)

Exit Sub

Badname:

MsgBox "Please revise the entry in B1." & Chr(13) _

& "It appears to contain one or more " & Chr(13) _

& "illegal characters." & Chr(13)

Range("B1").Activate

End Sub

Thanks

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

Andreas Killer 144.1K Volunteer Moderator

Then we should clarify what exactly you mean by "dynamic name".

Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAn...

Andreas.

0 comments

Answer 2

Anonymous

Hi,

Thanks for this. I have done as you mentioned though it now means that the sheet name doesn't dynamically update and i have the same problem that once the hidden sheet closes and is hidden it doesn't then reopen.

Cheers

0 comments

Answer 3

Remove all code from all sheets

Remove all code from the code module ThisWorkbook

Copy all code below into the code module ThisWorkbook

Andreas.

Option Explicit
Option Compare Text

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
  Dim Dest As Range
  Set Dest = Range(Target.SubAddress)
  Select Case Sh.Name
    Case "Master"
      'Jump into a hidden sheet
      Dest.Parent.Visible = xlSheetVisible
      Dest.Parent.Select
      Dest.Select
    Case Else
      'Jump to the master sheet
      Dest.Parent.Select
      Dest.Select
      Sh.Visible = xlSheetHidden
  End Select
End Sub

Share via

Hyperlink to hidden sheet with dynamic name

3 answers