Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Hyperlink to spreadsheet tabs

    Hi,

    I use Hyperlinks to quickly get from one sheet or Tab to another in some of my workbooks.

    To do this I currently have the Hyperlink point to a specific cell address in the relevant sheet or Tab (eg: Sheet1!.

    This is unsatisfacory at times because I want to go to the place in the sheet where it was when I left it, not the cell address specified in the hyperlink.

    Is there any way that I can specify a Hyperlink which will work identically to clicking on the Tab, and take me to wherever I was on the relevant sheet.

    I have also found that using a Range Name is not satisfactory as again it is not flexible enough - it will always take me to the same place in the sheet or Tab.

    I note if I leave the Cell Ref blank in the Hyperlink Window when creating a Hyperlink, Excel inserts "A1" for me, and takes me to cell A1.

    Excel Help seems to only talk about cell addresses when going to a location within a workbook or sheet.

    Any suggestions greatly appreciated.

    And thanks for reading.

    Peter Moran

  2. #2
    New Lounger
    Join Date
    Aug 2015
    Location
    Nottingham
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can't you simple associate a click to the relevant tab to a keyboard shortcut macro

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Peter,

    This can be done easily through VBA. In the attached workbook there is a sheet Called "Main" with 3 Hyperlinks to sheets 1 through 3 respectively. The code will remember the last cell you had selected on each sheet. Going back to "Main" and clicking on the hyperlink you will be linked to the last selected cell from that sheet.

    HTH,
    Maud
    Attached Files Attached Files

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    petermoran (2015-08-15)

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Learning VB...

    Maud, why does that work?

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Maud has a Hidden sheet (xlveryHidden) that stores the last cell referenced. The items in main are not hyperlinks made to look like them. Maud uses the SelectionChange event to run a macro that does the work by activating the sheet then selecting the appropriate cell address from the hidden sheet and selecting that cell. Nice work Maud! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    petermoran (2015-08-15)

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    KW,

    Here is the logic. In each sheet's worksheet_SelectionChange event code, every time a new cell is selected, the target address gets written to a hidden sheet.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets("Hidden").Range("A1") = Target.Address 'WRITE ADDRESS TO HIDDEN SHEET
    End Sub
    On the main sheet's Worksheet_SelectionChange event, code will detect which hyper link was selected then open that sheet at the last selected cell by pulling the address back from the hidden sheet

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub 'IF MORE THAN 1 CELL SELECTED EXIT SUB
    If Not Intersect(Target, Range("A1:A3")) Is Nothing Then 'IF CELLS A1 THROUGH A3 SELECTED THEN
        With Worksheets("Hidden")
        Select Case Target.Address 'GET WHICH HYPERLINK SELECTED A1, A2, OR A3
            Case "$A$1"
                Sheet1.Activate
                Sheet1.Range(.Cells(1, 1)).Select 'RETRIEVE THE ADDRESS FROM HIDDEN SHEET
            Case "$A$2"
                Sheet2.Activate
                Sheet2.Range(.Cells(2, 1)).Select 'RETRIEVE THE ADDRESS FROM HIDDEN SHEET
            Case "$A$3"
                Sheet3.Activate
                Sheet3.Range(.Cells(3, 1)).Select 'RETRIEVE THE ADDRESS FROM HIDDEN SHEET
        End Select
        End With
    End If
    End Sub
    HTH,
    Maud

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    petermoran (2015-08-15)

  10. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Thanks RG

  11. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks to both of you...I thought the sheet was hidden, but then I could unhide it. VERY hidden is an attribute I don't understand.
    How do you make a sheet very hidden vs just hidden and then the reverse (unhide very hidden) ??

    AH HA...found it...in the properties window. Never Mind!
    Last edited by kweaver; 2015-08-15 at 12:22. Reason: solved my own question

  12. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Guys,

    Sorry for my absence and thanks for the input, especially Maudibe.

    I will give it a try, but it seems a complicated way to do what Excel does very simply itself!

    Maybe there should be a specific reference to link into Excel and do what it does when you return to a page, which would save all of Maudibe efforts!

    Thanks again guys.

    Peter Moran

  13. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi kweaver

    ..
    VERY hidden is an attribute I don't understand
    A hidden sheet can be easily 'unhidden' using the top-panel Ribbon
    (Format>'Hide & Unhide'>'Unhide Sheet..')
    Any Hidden sheets will appear in this list.

    ..but if you set a sheet to be 'Very Hidden' (using vba settings) then this will NOT appear in this list.
    ..It's like the same as making macros appear or not in the macros list.

    zeddy
    •Excel Treatment Coordinator

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    ..It's like the same as making macros appear or not in the macros list
    As an FYI to Zeddy's comparison, macros can be hidden using 3 methods:
    1. Declaring the macro as Private instead of Public:

    Code:
    Private Sub MyMacro()
    --code--
    End Sub
    Use this method only if the macro serves a purpose within the module it is written. If it is called from another module then it must remain Public.

    2. Assigning an unused optional parameter will prevent it from showing in the Macro List:

    Code:
    Public Sub MyMacro(Optional DummyParameter As Integer)
    --code--
    End Sub
    Note: "Optional" relieves the burden on the programmer to go back and revise each instance of a call to the macro to include a parameter. Also note that the parameter can be any valid parameter name but the type must be anything other than Variant. Leaving out the type will default to variant and the macro will then becom visible in the Macro List such as:

    Code:
    Public Sub MyMacro(Optional DummyParameter)
    --code--
    End Sub
    3. Converting the Sub to a Function that does not accept a parameter or return a value

    Code:
    Public Function Mysub()
    --code--
    End Function
    Note: If a return value has not been set, a function will still return false but unused.

    Maud

  15. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    I would do this by trapping the double click event on a worksheet and taking the cell value as the name of the sheet that needed to be activated. The following macro can be added to any worksheet where you want this behaviour
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim bSheetExists As Boolean
      On Error Resume Next
        bSheetExists = (Sheets(Target.Value).Name <> "")
      On Error GoTo 0
      If bSheetExists Then Worksheets(Target.Value).Activate
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  16. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Andrew

    ..that works great provided the double-clicked cell corresponds to a sheet that is not hidden.
    ..You could end up unintentionally on a different sheet otherwise.
    ..or even in Edit-mode for the double-clicked cell

    zeddy
    •Senior Beverage Consultant
    .

  17. #14
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    620
    Thanks
    166
    Thanked 76 Times in 67 Posts
    Quote Originally Posted by Andrew Lockton View Post
    trapping the double click event on a worksheet
    Sheets are selected via single click within a book, not double click.

  18. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Andrew,

    Double clicking a hyperlink may be a bit impractical.

    Your code would work great only if:
    1. The value of the double clicked cell is the name of the sheet however, the OP gives no indication of what that cell value is; only that it is a hyperlink to a specific sheet cell address.

    2. It is possible to double click a hyperlinked without it jumping immediately to the hyperlink address

    I would prefer your concept if you could pull the target sheet name from the hyperlink instead of from the cell value:

    s = Split(Target.Hyperlinks(1).SubAddress, "!")
    bSheetExists = s(0)

    It is a double edge sword in that to pull the sheet name from a hyperlinked cell, the cell would have to contain a hyperlink. But clicking on a hyperlink directs you to the hyperlink address before the hyperlinked sheet could be extracted.

    Therefore, that is why I made a cell look like a hyperlink. Applying your code to a cell that looks like a hyperlink (but is not) is doable but the value of the cell would have to be the sheet name.

    Maud

Page 1 of 2 12 LastLast

Posting Permissions

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