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

 

[riskintelligence]

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

 

[Jaafar Tribak]

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.

 

[riskintelligence]

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.

 

[riskintelligence]

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

 

[Jaafar Tribak]

Try changing the cmndbrs_OnUpdate event routine to this :

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

 

[riskintelligence]

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.

 

[Jaafar Tribak]

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.