Results 1 to 4 of 4
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Link to another sheet

    Poor title, but here's what I would like to do:

    I would like to be able to click on a date entry in one sheet and have it take me to the same date on another sheet (same workbook). I'm pretty sure I've seen this done, but I just don't recall where (or how). The idea is to be able to click on the date and go to a separate memo sheet for comments/notes; this is to keep the sheet(s) a little cleaner. The "Comment" function is cool, but doesn't work for me in this case. Also, there is a comment for every day of the year, so I will need to do this for 365 cells.

    Any help greatly appreciated!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Two options I can think of:

    1. Set up a hyperlink in the cells with the dates in Sheet1, pointing to the location to go to in Sheet2. Generate Named Ranges in Sheet2 for the jump-to locations.

    2. Create a macro in Sheet1 for the BeforeDoubleClick event to take you to the correct location on Sheet2. The example below will position the cursor in the same row and column in Sheet2 when a cell in Sheet1 is double-clicked. You need to customize the .Activate line to position the cursor where you want for your application.
    Code:
    Option Explicit
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Worksheets("Sheet2").Activate
        ActiveSheet.Cells(Target.Row, Target.Column).Activate
    End Sub
    This macro is copied to the Sheet1 worksheet code page, which can be accessed by right-clicking on the Sheet1 tab and selecting "View Code".
    PJ in FL

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Sorry for the delay, I thought I would have received a notice when someone replied.

    I like the second option you suggest, but don't really understand how to enter the .Activate variables. The range I am looking at is A5 through A370. Thanks for your help with this . . . and I will look at my settings to see about notifications (I guess that's where to look - I've been away for a while).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Thumbs up

    Quote Originally Posted by pjustice57 View Post
    Two options I can think of:

    1. Set up a hyperlink in the cells with the dates in Sheet1, pointing to the location to go to in Sheet2. Generate Named Ranges in Sheet2 for the jump-to locations.

    2. Create a macro in Sheet1 for the BeforeDoubleClick event to take you to the correct location on Sheet2. The example below will position the cursor in the same row and column in Sheet2 when a cell in Sheet1 is double-clicked. You need to customize the .Activate line to position the cursor where you want for your application.
    Code:
    Option Explicit
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Worksheets("Sheet2").Activate
        ActiveSheet.Cells(Target.Row, Target.Column).Activate
    End Sub
    This macro is copied to the Sheet1 worksheet code page, which can be accessed by right-clicking on the Sheet1 tab and selecting "View Code".
    Actually, I see where the result I wanted is accomplished by what you provided - I don't need to do anything further. I did copy the same code to the destination sheet so I have an easy way to return. Thanks again for the help PJ, this works very well!
    Last edited by bfxtrfcmgr; 2011-05-21 at 17:09.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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