Hyperlink To Top Left Corner

  • Thread starter Thread starter Lewis Clark
  • Start date Start date
L

Lewis Clark

Hello, Group!

I have a workbook with multiple worksheets in Excel 2003. I would like to set up hyperlinks from the summary sheet (my main worksheet) to the individual sheets. When I activate a link to an individual sheet, I would like the linked cell to appear in the upper left corner of the viewing area.

Is this possible, preferably without macros? Or do I need to link to a cell in a higher-numbered row to force the desired row higher on the screen?

Thank you in advance!
 
Hi Lewis,
You would need a macro, my preference would not be the upper left corner but
some rows before and after, and some columns before and after.

Since you want it for the entire workbook you would install into ThisWorkbook
Right click on the Excel logo at left of the menu bar

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

I would find that very annoying, especially if I don't see columns to the left (if any)..

My own preference if I don't like the position would be to use something like the
ShowTopLef5 macro (but ONLY occasionally used) found in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#topleft

More information on event macros in
http://www.mvps.org/dmcritchie/excel/event.htm



Hello, Group!

I have a workbook with multiple worksheets in Excel 2003. I would like to set up hyperlinks from the summary sheet (my main
worksheet) to the individual sheets. When I activate a link to an individual sheet, I would like the linked cell to appear in the
upper left corner of the viewing area.

Is this possible, preferably without macros? Or do I need to link to a cell in a higher-numbered row to force the desired row
higher on the screen?

Thank you in advance!
 
David,

As much of a novice as I am with macros, this was easy for me to implement. Thank you very much.

The individual sheets are narrow, only 1 screen wide, so some of the issues you mention are moot in this application. I really just need this to locate the cursor vertically on the sheet, and it works perfectly.

Thanks again!


--

Hi Lewis,
You would need a macro, my preference would not be the upper left corner but
some rows before and after, and some columns before and after.

Since you want it for the entire workbook you would install into ThisWorkbook
Right click on the Excel logo at left of the menu bar

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

I would find that very annoying, especially if I don't see columns to the left (if any)..

My own preference if I don't like the position would be to use something like the
ShowTopLef5 macro (but ONLY occasionally used) found in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#topleft

More information on event macros in
http://www.mvps.org/dmcritchie/excel/event.htm



Hello, Group!

I have a workbook with multiple worksheets in Excel 2003. I would like to set up hyperlinks from the summary sheet (my main
worksheet) to the individual sheets. When I activate a link to an individual sheet, I would like the linked cell to appear in the
upper left corner of the viewing area.

Is this possible, preferably without macros? Or do I need to link to a cell in a higher-numbered row to force the desired row
higher on the screen?

Thank you in advance!
 
Mr. David McRitchie,
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

1. This is a piggy-back query ; am taking a free ride.

2. Enter an Excel workbook with 2 worksheets.

3. Sheet1 contains a list of Hyperlinks to the vaious locations on
Sheet2.

4. The (Sheet1) Hyperlinks are formulae such as follows :-

5. HYPERLINK("#"&ADDRESS(D5-1,COLUMN('Volume 1&2'!$C$1),1,TRUE,
CELL("FileName",'Volume 1&2'!$A$1)),
INDIRECT(CHAR(39)&CELL("FileName",'Volume 1&2'!$A$1)&CHAR(39)&CHAR(33)&
ADDRESS(D5,COLUMN('Volume 1&2'!$D$1))))

6. However, while the (Sheet1) Hyperlinks land in Sheet2, it does not
trigger the event of,

Private Sub Workbook_SheetFollowHyperlink, as given above.

7. Have attempted Excel 2000 & 2002, but in vain. Is there any
alternative ?

8. Please share your experience. Regards.
 
I couldn't get the hyperlinks added with the =hyperlink() worksheet formula to
fire the Worksheet_FollowHyperlink event either.

I think you'll have to use Insert|hyperlink to get that event to fire--or find a
different approach.
 
Mr. Dave Peterson :-

Thank you for your reply ; Is there anything else that would nudge or
budge a bit (hopefully) by virtue of the Hyperlink() being triggered ?

Note that the Help File has stated that SheetFollowHyperlink would be
triggered by any hyperlink ; but, no grudge though.

Regards.
 
Not that I know.

Mr. Dave Peterson :-

Thank you for your reply ; Is there anything else that would nudge or
budge a bit (hopefully) by virtue of the Hyperlink() being triggered ?

Note that the Help File has stated that SheetFollowHyperlink would be
triggered by any hyperlink ; but, no grudge though.

Regards.
 
Hi (e-mail address removed),
I am guessing that these links are only on the first tab, and that you
use the BACK button (hopefully on your 5-button mouse) to return.

If you are going to use the HYPERLINK Worksheet Function, you
might use sheet activate instead. As I said before I can't stand the
effect but you are probably linking only to column A so would not be
as bad as in my tests.

The following installed into ThisWorkbook
would not do anything on the first worksheet with the Exit Sub..
Nor will it be triggered if hyperlinking to a location on the same work sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sheets(1).Name = ActiveSheet.Name Then Exit Sub
Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

Please use your name when posting (in Email and/or signature),
I hadn't noticed at first that you were not the original poster. It is a lot
more pleasant to reply to someone in the newsgroups with a real name.
 
Just a word of warning...

I don't think that following a hyperlink will fire the activate event in all
versions of excel. I think I was involved in a discussion with an xl2k user
that had that problem. IIRC, xl2002+ works ok.
 
Hi Dave P,
The worksheet activate should work for the second poster using the worksheet function..
Your answer might be a bit confusing, since your are referring to the original suggestion
of followhyperlink and not to the worksheetactivate to which you replied.
 
Nope, I was referring to the activate event. I may be misremembering the facts,
but I do believe that someone running xl2k won't be able to rely on that.

Maybe someone running xl2k could test it to see if insert|hyperlink and
=hyperlink() will cause the worksheet activate event to fire.
 
sheetactivate won't fire if hyperlink is to the same page, but my Excel 2000
(which I like better) is broken and is why I am on Excel 2002.
 
When you get xl2k fixed (reinstall or help|detect and repair?), I think you'll
see that even hyperlinks to other pages won't fire that event.
 
David McRitchie,
Please use your name when posting (in Email and/or signature),
I hadn't noticed at first that you were not the original poster. It is a lot
more pleasant to reply to someone in the newsgroups with a real name.

Thank you for your reply.

I'm using my real name ; that's the name my colleagues would use
(affectionately) to call me in the workplace. That's ace to heart.

The same name has been used consistently since the very first post in
this neighbourhood. Others had replied (to my query) quoting exemplary
range of K1:T10 ; but, no grudge though.

Had Jimmy not (preferentially) called Malcom, John (in the last
century), it would leave many guessing what should that J stand for.
Clarification should draw those light-years apart together.

Regards.
 
David McRitchie,


Thank you for your reply.

I'm using my real name ; that's the name my colleagues would use
(affectionately) to call me in the workplace. That's ace to heart.

The same name has been used consistently since the very first post in
this neighbourhood. Others had replied (to my query) quoting exemplary
range of K1:T10 ; but, no grudge though.

Had Jimmy not (preferentially) called Malcom, John (in the last
century), it would leave many guessing what should that J stand for.
Clarification should draw those light-years apart together.

Where is your name in the posts, I can only see "Regards,"?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
Mr. Peo Sjoblom,

Thank you for your reply. I must hasten to respond without digression ;
Please share your insight of activating Hyperlink() with reference to
the consequential events being triggered (Excel 2000).

Regards.

(My name is not repeated since the name has already appeared at the
first line of this post ; the truncation of name is GGVT's doing and
therefore, no grudge though).
 
Back
Top