Link to another sheet

Topic

New Reply

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!

Reply | Quote

Replies

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”.

Reply | Quote

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).

Reply | Quote

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!

Reply | Quote