Allow Access to Hidden Sheets

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Jaafar Tribak

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

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

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

Jaafar Tribak

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

riskintelligence

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

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

Watch MrExcel Video

Forum statistics

Threads
1,099,077
Messages
5,466,473
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top