VBA- Hyperlink to existing sheet in workbook that is hidden

Question

Im hyperlinking cells based on their values to existing sheets in my workbook. It runs through Column B and for any instance of the word "title" it places a hyperlink in the cell directly under it. The string in the cell directly under it is the same as what the corresponding sheet name would be. The script works perfectly however if the sheet is hidden it wont open it. Can anyone advise how to solve for this?

'Loops through a specified column and when a specified value is found, puts a hyperlink in the cell below

  Const cWsName As String = "Title Detail"
  Const cSearch As String = "Title"
  Const cRow1 As Integer = 1
  Const cRow2 As Long = 200
  Const cCol As String = "B"

  Dim oWb As Workbook
  Dim oWs As Worksheet
  Dim rCell1 As Range
  Dim rCell2 As Range
  Dim iR As Integer
  Dim strText As String
  Dim strAddr As String

  Set oWb = ActiveWorkbook
  Set oWs = oWb.Worksheets(cWsName)
  For iR = cRow1 To cRow2
    Set rCell1 = oWs.Range(cCol & iR)
    Set rCell2 = oWs.Range(cCol & iR + 1)
    strText = rCell2.Text 'What's written in the cell.
    strAddr = rCell2.Address 'The address e.g. B1, B13 ...
    If rCell1 = cSearch Then
      If strText <> "" Then
        'Anchor is the place where to put the hyperlink, cell or object.
        'Notice the single quotes (') in the SubAddress.
        rCell2.Hyperlinks.Add _
        Anchor:=rCell2, _
        Address:="", _
        SubAddress:="'" & rCell2 & "'!" & "A1", _
        TextToDisplay:=strText 'The same text as requested
       Else
        'what to do if the cell below the Title cell is empty.
        End If
    End If
  Next

  'End on Title Detail Sheet
  oWb.Sheets("Title Detail").Select

End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim oWs As Workbook
Dim targetString As String, targetSheet As Worksheet
Set oWs = ActiveWorkbook 
targetString = Target.TextToDisplay 
Set targetSheet = oWs.Worksheets(targetString)
If targetSheet.Visible = False Then
    targetSheet.Visible = True
End If

'End on Title Detail Sheet targetSheet.Select

End Sub

Have you tried unhiding the sheet in the Worksheet_FollowHyperlink event of the worksheet? — nutsch
@nutsch I tried adding a private sub Worksheet_FollowHyperlink (ByVal Target As Hyperlink) but I cant get it to work? Am I doing something obvious incorrectly? — Ranger9

Comintern Comintern · Accepted Answer · 2018-10-02T23:23:21

As I pointed out in my answer to the similar question that you deleted:

Note that you need to explicitly call .Follow if the sheet is hidden.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim oWs As Worksheet
    Dim targetString As String, targetSheet As Worksheet

    Set oWs = ActiveWorkbook

    targetString = Target.TextToDisplay

    Set targetSheet = oWs.Sheets(targetString)

    If targetSheet.Visible = False Then
        targetSheet.Visible = True
        'If the sheet was hidden, you have to explicitly follow the link again.
        Application.EnableEvents = False
        Target.Follow
        Application.EnableEvents = True
    End If
End Sub

VBA- Hyperlink to existing sheet in workbook that is hidden

1 Answers