Allow Access to Hidden Sheets

[riskintelligence]

Greeting,

I need to create a workbook which only has the first sheet visible to force the user to go through a process.

On the first sheet I'm using the HYPERLINK function in conjunction with a VLOOKUP to create a variable hyperlink to multiple possible destination within the same workbook.

Question is, is it possibly to allow access to those destination sheets whist keeping the sheet tabs hidden or inaccessible to the user, forcing them to use the hyperlink, which ensures they reach the right destination, and how to do this?

Thanks for any advice.

Colin

 

[Leith Ross]

Hello Collin,

Set the worksheet Visible property to xlSheetVeryHidden will prevent the user from making the sheet visible from the Ribbon. The only way it can be made visible again is through VBA or the VB Editor.

A very hidden sheet is accessible through VBA code only.

 

[riskintelligence]

Hi Leith, thank you for responding. In this case I need the sheets hidden yet accessible via a hyperlink on the first (and visible) sheet.

 

[RoryA]

You can’t hyperlink to a hidden sheet, no. You could simply hide the sheet tabs, or you could, as Leith said, use code for the actual navigation.

 

[riskintelligence]

Thanks Rory, I'll have to dust off my VBA!

Cheers.

 

[Jaafar Tribak]

On the first sheet I'm using the HYPERLINK function in conjunction with a VLOOKUP to create a variable hyperlink to multiple possible destination within the same workbook.Colin

What is the exact Hyperlink formula including the VLOOKUP ?

If we can extract the exact destination cell(s) from the formula then we can use a workaround to unhide the hidden sheet and select the target range.

 

[riskintelligence]

Hi Jaafar,

Formula is =HYPERLINK("#"&"'" & G16 & "'!" & G17,G18)

G16 contains the Sheet reference, which changes based on a user selection from a combo box.

Let's say user selects option "x" which gives a value of 1 in the combo box cell link ( cell F16 ). Value 1 relates to "x" and "sheet1" on the table array for the VLOOKUP. The VLOOKUP in G16 returns the value "sheet1" which is where the HYPERLINK formula takes the user.

Is the user selected option "y" on the combo box, the destination changes based on the VLOOKUP of the table array.

I hope this makes sense.

Interested to see your solution

Colin

 

[Jaafar Tribak]

Ok- The following code assumes the following:

1- Sheet1 is the only visible sheet and the one that contains the Hyperlink(s) formula(es).
2- Cell A1 in Sheet1 is the cell with the Hyperlink Formula.
3- Cell G16 in Sheet1 has the name of the Target Sheet which can be changed dynamically.
4- Cell G17 in Sheet1 has the address of the Target Range which can be changed dynamically.
5- Cell G18 in Sheet1 has the hyperlink Friendly Name.

Workbook Demo


Place theis code in the ThisWorkbook Module:

Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function GetForegroundWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetCursor Lib "user32" () As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function GetForegroundWindow Lib "user32" () As Long
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetCursor Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private WithEvents cmndbrs As CommandBars


Private Sub Workbook_Open()
    Call HookCommandBars
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If ActiveSheet Is Sheet1 And Not Sh Is Sheet1 Then Sh.Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call HookCommandBars
End Sub

Private Sub HookCommandBars()
    Set cmndbrs = Application.CommandBars
    Call cmndbrs_OnUpdate
End Sub

Private Sub cmndbrs_OnUpdate()

    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Static hCur As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Static hCur As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Static KeyState As Long

    Dim tCurPos As POINTAPI
    Dim oRangeUnderCursor As Object
    Dim sTargetSheetName As String, sTargetRange As String    
    
    With Application.CommandBars.FindControl(ID:=2040)
        .Enabled = Not .Enabled
    End With
    
    If GetForegroundWindow = FindWindow("wndclass_desked_gsk", vbNullString) Then
        Set cmndbrs = Nothing: Exit Sub
    End If
    
    Call GetCursorPos(tCurPos)
    On Error Resume Next
    Set oRangeUnderCursor = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)
    If TypeName(oRangeUnderCursor) = "Range" Then
        If InStr(oRangeUnderCursor.Formula, "=HYPERLINK") Then
            sTargetSheetName = Split(oRangeUnderCursor.Formula, "&")(2)
            sTargetRange = Split(Split(oRangeUnderCursor.Formula, "&")(4), ",")(0)
            If Sheets(Evaluate(sTargetSheetName).Text).Visible <> xlSheetVisible Then
                If KeyState <> GetKeyState(VBA.vbKeyLButton) Then
                    If GetCursor <> hCur Then
                        Application.OnTime Now, "'" & Me.CodeName & ".Follow_Hyperlink """ & _
                        sTargetSheetName & """,""" & sTargetRange & "'"
                    End If
                End If
            End If
        Else
             hCur = GetCursor
        End If
    End If
    KeyState = GetKeyState(VBA.vbKeyLButton)

End Sub


Private Sub Follow_Hyperlink(ByVal ShName As String, ByVal RngAddr As String)

    Dim oSheet As Worksheet
    Dim GetCutCopyRange As Range
    
    Set GetCutCopyRange = CallByName(Sheets(Evaluate(ShName).Text), "Range", VbGet, Range(RngAddr))
    Sheets(Evaluate(ShName).Text).Visible = xlSheetVisible
    Application.Goto GetCutCopyRange, True

End Sub

The code should work with all hyperlink cells not just with cell A1 as long as they follow the same formula pattern as that of A1... ie:= =HYPERLINK("#"&"'" & Cell_REF & "'!" & Cell_REF,Cell_REF)

Regards.

 

[riskintelligence]

Thanks Jaafar,

Unable to resolve an issue with this line:

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Returns a compile error:

"Only comments may appear after End Sub, End Function, or End Property"

The first sheet is named START ASSESSMENT. I replaced references to Sheet1 accordingly.

 

[Jaafar Tribak]

Thanks Jaafar,

Unable to resolve an issue with this line:

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Returns a compile error:

"Only comments may appear after End Sub, End Function, or End Property"

The first sheet is named START ASSESSMENT. I replaced references to Sheet1 accordingly.

The error you are getting suggests that you have a routine(s) (macro(s)) above that line.... Do you already have some existing code in the ThisWorkbook Module ?

The following section of my code should be placed right at the top of the module before anything else :

Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function GetForegroundWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetCursor Lib "user32" () As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function GetForegroundWindow Lib "user32" () As Long
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetCursor Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private WithEvents cmndbrs As CommandBars

Also, just replace in the code Sheet1 with Sheets("START ASSESSMENT")