Object or picture hyperlink to follow hidden sheets

  • Hi Guys I have a workbook with a dashboard that has picture objects. These pictures are hyperlinked to sheets in the workbook but I cannot open the sheets when the sheets are hidden. What code can I use to get picture object hyperlink to open linked hidden sheets then return to dashboard? Thanks Guys

  • If you insert the images as ActiveX objects (found in the developer>controls>insert menu rather than Insert>Picture) then it will allow you to easily assign code to various actions like a single left mouse click.

    You can then attach the appropriate VBA code to unhide the appropriate sheet and move the focus to the place on that sheet you want the hyperlink to take you to. So it would work just like a hyperlink but would ensure that the sheet is unhidden first.

    Then for the various sheets just have them all contain a Worksheet_Deactivate event which re-hides the sheet on leaving it.

    Code attached to the ActiveX Picture object would be something like this:

    Private Sub AccessWorksheet()
        ThisWorkbook.Worksheets("WorksheetNameToAccessHere").Visible = xlSheetVisible
    End Sub

    Although I'd be inclined to move the functionality out to another subroutine in a code module and just pass the name of the worksheet to that from each ActiveX control rather than coding the unhide functionality each time.

    Then something like the following held in each hidden worksheet in order to re-hide on leaving it;

    Private Sub Worksheet_Deactivate()
        Me.Visible = xlSheetVeryHidden
    End Sub

    Hopefully that gets you started.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!