Double-Click "Hyperlink" to Hidden Sheets

[CodingMonkey]

Hey All,

I've come here with a question about a code that I'm currently running to act as a method to "Hyperlink" to hidden sheets. I found the code on this forum, but it was back from 2011. The code works perfectly well until MERGED cells.

Private Sub Worksheet_Activate()    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Library" Then
            sh.Visible = xlSheetHidden
        End If
    Next sh
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Address
        Case "$A$3"
            Sheets("496").Visible = True
            Sheets("496").Activate
        Case "$A$8"
            Sheets("497").Visible = True
            Sheets("497").Activate
    End Select
End Sub

Like I said, the code works perfectly unless the cell is merged. Even though the range of the cell is "A3" it won't activate the DoubleClick code. But single cells, such as A8, that are not merged work smoothly.

Is there something that I'm missing in my code to allow the code to work for merged cells?

The object of my worksheet is as follows:

>Double-click on Logbook number in specific columns (A, E, I, M, Q)
>Upon double-click: Opens hidden sheet for that specific logbook that allows user to see its Table of Contents.
>Upon clicking back to main sheet "Library": Logbook sheet reverts to hidden to avoid any clutter.​

 

[Jeffrey Mahoney]

Please try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim Cel As Range
  Dim i As Range
  If Not Intersect(Target, Range("A3")) Is Nothing Then
    Cancel = True
    Sheets("496").Visible = True
    Sheets("496").Activate
    Exit Sub
  End If
  If Not Intersect(Target, Range("A8")) Is Nothing Then
    Cancel = True
    Sheets("497").Visible = True
    Sheets("497").Activate
    Exit Sub
  End If
  
End Sub

 

[CodingMonkey]

That works perfectly. Thank you very much!

Updated Code for anyone looking into this in the future. You can add as many ranges in the 2nd portion. Just copy and paste and change appropriately.

Private Sub Worksheet_Activate()    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Library" Then
            sh.Visible = xlSheetHidden
        End If
    Next sh
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim Cel As Range
  Dim i As Range
  If Not Intersect(Target, Range("A3")) Is Nothing Then
    Cancel = True
    Sheets("496").Visible = True
    Sheets("496").Activate
    Exit Sub
  End If
  If Not Intersect(Target, Range("A8")) Is Nothing Then
    Cancel = True
    Sheets("497").Visible = True
    Sheets("497").Activate
    Exit Sub
  End If
  
End Sub