open hidden sheet with dynamic hyperlink

[mctown]

Hello,

Am new here and I don't know if I should create a new thread but my question is related to dynamic hyperlinks.
Anyways, I created a dynamic hyperlink that can jump to desired sheet based on the sheet name. It works fine. The issue is that the dynamic hyperlink won't work if sheet are hidden.

I have tried the VBA codes on YouTube: Learn Excel from MrExcel - "Hyperlink to a Hidden Worksheet" - Podcast #1729

It only works if sheet hyperlink is created using "Ctrl K" process.

Would appreciate any help.

Thanks in advance!

dynamic-hyperlink.xlsx
	A	B	C
2	Type sheet name here ->	sheet1	sheet1
3
4	Sheets Names
5	sheet1	active
6	sheet2	hidden
main

Cell Formulas
Range	Formula
C2	C2	= HYPERLINK("#" &B2& "!A3",B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C2	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=#REF!#

 

[Jaafar Tribak]

I remember coming up with a hacky workaround. Take a look and see if that helps :

Hyperlink to hidden sheet using a cell with the =Hyperlink formula

Hi, Hoping that someone can help with a hyperlink / VBA problem that's driving me crazy!? Basically, I have a workbook with a front sheet which contains a column of hyperlinks to other sheets within the same workbook. Each of these hyperlinks is built using the =HYPERLINK formula rather than a...

www.mrexcel.com

 

[mctown]

I remember coming up with a hacky workaround. Take a look and see if that helps :

Hyperlink to hidden sheet using a cell with the =Hyperlink formula

Hi, Hoping that someone can help with a hyperlink / VBA problem that's driving me crazy!? Basically, I have a workbook with a front sheet which contains a column of hyperlinks to other sheets within the same workbook. Each of these hyperlinks is built using the =HYPERLINK formula rather than a...

www.mrexcel.com

Hi Jaafar,
Thanks for your reply. I used the codes but when I tried making the name of the sheet variable (i.e. sheet name = text contained in a reference cell). It doesn't work. Any help to resolve this would be appreciated. Thanks again.

 

[Jaafar Tribak]

Try this variation :

Code goes in the ThisWorkbook Module:

Option Explicit

Private WithEvents CmndBars As CommandBars

Private Type POINTAPI
    x As Long
    Y As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare Function GetActiveWindow Lib "user32" () As Long
#End If


Private Sub Workbook_Activate()
    Set CmndBars = Application.CommandBars
    Call CmndBars_OnUpdate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set CmndBars = Application.CommandBars
    Call CmndBars_OnUpdate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set CmndBars = Nothing
End Sub


Private Sub CmndBars_OnUpdate()

    Static lPrev As Long
    Dim tCurPos As POINTAPI
    Dim oObj As Object, oTargetSheet As Worksheet
    Dim sFriendlyName As String, sTargetRangeAddr As String

    If Not ActiveWorkbook Is Me Or GetActiveWindow <> Application.Hwnd Then Exit Sub

    With Application
        Call GetCursorPos(tCurPos)
        Set oObj = .ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.Y)
        If TypeName(oObj) = "Range" And .ActiveWindow.RangeSelection.Count = 1 Then
            If InStr(1, oObj.Formula, "=HYPERLINK") Then
                If lPrev <> GetKeyState(VBA.vbKeyLButton) Then
                    sFriendlyName = Evaluate(oObj.Formula)
                    Set oTargetSheet = Worksheets(sFriendlyName)
                    If oTargetSheet.Visible <> xlSheetVisible Then
                        oTargetSheet.Visible = xlSheetVisible
                        Application.Goto oTargetSheet.Range("a1"), True
                    End If
                End If
            End If
        End If
    End With
    lPrev = GetKeyState(VBA.vbKeyLButton)
End Sub