Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink to hidden sheets (E2000 with ASAP utils)

    Subject edited by HansV to be more informative

    Morning

    Reading various other Posts on making an index / menu page I took a loungers advice and downloaded ASAP utilities which created said menu exactly how I wanted it, trying to be smart however I thought it would be nice to hide the worksheets and only have the index page showing with the hyperlinks to them.

    By doing this the hyperlinks stop working, is there anyway that you can think of whereby I can hide the worksheets and still keep the hyperlinks on the index page.

    TIA

    Stephen

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to hidden sheets (E2000 with ASAP utils)

    What would you expect a hyperlink to a hidden sheet to do?

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to hidden sheets (E2000 with ASAP utils)

    Hi Hans

    I, perhaps naively, thought that the hyperlink would 'unhide' that particular worksheet temporarily, similar to an Access database menu.

    Thanks

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to hidden sheets (E2000 with ASAP utils)

    A hyperlink doesn't do that. You would need code to unhide and rehide the worksheet.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hyperlink to hidden sheets (E2000 with ASAP utils)

    Instead of using hyperlinks, you need as Hans mentioned a macro. A technique:

    Add a LABEL from the FORMS toolbar to the cell(s) you want to "hyperlink"
    right-click and Edit the text and remove it all so it is blank.
    right click on the label and assign macro to the macro below:

    <pre>Sub UnhideGotoHide()
    Dim wks As Worksheet
    Set wks = Worksheets(ActiveSheet.Shapes(Application.Caller) _
    .TopLeftCell.Value)
    With wks
    .Visible = xlSheetVisible
    .Activate
    .Range("A1").Activate
    End With
    End Sub</pre>


    Even though the label is not visible, it will still have a finger icon so will LOOK and act like a link (except the back arrow will not work). in the cell add the sheet name to goto and format it as desired (you can underline and make text blue so it looks like a hyperlink.

    You will also need an object to return to index page and rehide the sheet. Create the objects on each of the hidden sheets and assign this macro:

    <pre>Sub ReturnToIndex()
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Worksheets("Index").Activate
    wks.Visible = xlSheetHidden
    End Sub</pre>


    All the objects on the hidden sheets will be assigned the same macro. All the objects on the index page will be assigned the other macro. The macro determines what object was pressed and determines the sheet to unhide and goto based on the cell contents.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to hidden sheets (E2000 with ASAP utils)

    You could also put the code to hide the worksheet in the worksheet deactivate event routine.
    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hyperlink to hidden sheets (E2000 with ASAP utils)

    A good suggestion, though I imagine you would want it not to hide the "index sheet". Then one could add the hiding to the deactivate event (as you suggest) and then you don't need a button on every sheet to goto the index, just select the index sheet from the tab and the other sheet will be hidden automatically.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •