Hyperlink To Top Left Corner

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

"Lewis Clark" <lewis_clark_644 @ yahoo.com> wrote in message news:U%fQf.2$o41.1@trnddc06...
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!


--

"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message news:uPbqABHRGHA.5728@tk2msftngp13.phx.gbl...
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...oc.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

"Lewis Clark" <lewis_clark_644 @ yahoo.com> wrote in message news:U%fQf.2$o41.1@trnddc06...
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.

tkt_tang@hotmail.com wrote:
>
> 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.

--

Dave Peterson

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.

tkt_tang@hotmail.com wrote:
>
> 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.

--

Dave Peterson

Hi tkt_tang@hotmail.com,
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


> tkt_tang@hotmail.com wrote:
> > 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 ?

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 McRitchie wrote:
>
> Hi tkt_tang@hotmail.com,
> 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
>
> > tkt_tang@hotmail.com wrote:
> > > 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 ?

--

Dave Peterson

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.

David McRitchie wrote:
>
> 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.

--

Dave Peterson

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 wrote:
>
> 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.

--

Dave Peterson

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.

<tkt_tang@hotmail.com> wrote in message
news:1142170287.017782.44920@v46g2000cwv.googlegroups.com...
>
> 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.
>

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

Very old post, but in case others are still searching, like I was, You can now put a range of cells as the link. So I put in A3:H3 and it loaded it better. Depending on the size of your viewing screen, you would have to adjust the left cell with the right cell.
I have all or my hyperlinks in column A, then froze it, so you do not need the back key, Just put a bunch of hyperlinks in the A column. For me I put 2014 Jan Feb March .... 2015 Jan Feb March... and so on. This was for Excel 2013