Hyperlink To Top Left Corner

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!
 
D

David McRitchie

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.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!
 
T

tkt_tang

Mr. David McRitchie,
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub
Click to expand...

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

Dave Peterson

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

tkt_tang

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

Dave Peterson

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.
Click to expand...
 
D

David McRitchie

Hi [email protected],
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.
 
D

Dave Peterson

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.

David said:
Hi [email protected],
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...
 
D

David McRitchie

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

Dave Peterson

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

David McRitchie

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

Dave Peterson

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

tkt_tang

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.
Click to expand...

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

Peo Sjoblom

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.
Click to expand...

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
 
T

tkt_tang

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