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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Thank you, that is resolved.

New compile error is:

"Only valid in object module"

Relates to:

WithEvents

I found this info on the .NET site, but unsure how to resolve. Any help appreciated: https://docs.microsoft.com/en-us/of...er-interface-help/only-valid-in-object-module

That indicates you are not placing the code in the intended module.

The code should be placed in the ThisWorkbook Module

- Press Alt+ F11 which should take you to the VBE
- In the upper-left corner of the VBE screen, you will find the Project explorer window.
- In side the Project explorer wiindow, double-click on the module that says 'ThisWorkbook' and paste the code I gave you inside the white pane that comes up.

i hope that helps.
 
Upvote 0
Thanks Jaafar,

I think I have a few complicating factors in the workbook which are causing problems. I see how it runs fine in your example sheet - thank you for that :)

As my workbook is still being developed, and will no doubt change over the coming days and possibly weeks, I might wait until it's finalised before applying this feature.

Thank you so much for your help, I'll post back here with an update in the next week or so.
 
Upvote 0
I have it working now, but in isolation of other factors.

Sorry, I didn't give you the full formula, only the hyperlink portion. When I isolate the hyperlink portion it runs fine. Full formula is:

=IF(L23="","",HYPERLINK("#"&"'" & $G$16 & "'!" & $G$17,$G$18))

This formula currently resides in C25.

I'm not sure why is doesn't run like this, even when relocated to A1.

The other complication I have is, I have "back" and "next" buttons on each page which I want the user to use to navigate through the sheets. These buttons are just hyperlinked text on inserted shapes.

Thanks again!

Colin
 
Last edited:
Upvote 0
Try changing the cmndbrs_OnUpdate event routine to this :
Code:
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") And Len(oRangeUnderCursor.Value) 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
 
Upvote 0
Thanks Jaafar,

This works on C25 now :)

Is there a way I can enable the text hyperlinks on the active sheet after leaving the first sheet?

Thanks again.
 
Upvote 0
Thanks Jaafar,

This works on C25 now :)

Is there a way I can enable the text hyperlinks on the active sheet after leaving the first sheet?

Thanks again.

Just set the font color of the cell to blue and underline the font from the Font menu.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top