Allow Access to Hidden Sheets

riskintelligence

New Member
Joined
Oct 24, 2019
Messages
13
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,862
Office Version
2010, 2007
Platform
Windows
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

New Member
Joined
Oct 24, 2019
Messages
13
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,188
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,427
Office Version
2016
Platform
Windows
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.
 
Last edited:

riskintelligence

New Member
Joined
Oct 24, 2019
Messages
13
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,427
Office Version
2016
Platform
Windows
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:
Code:
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

New Member
Joined
Oct 24, 2019
Messages
13
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.
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,427
Office Version
2016
Platform
Windows
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 :
Code:
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")
 

Forum statistics

Threads
1,078,520
Messages
5,340,931
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top