Hyperlinks:Is there any way of getting around this?

[mevetts]

Hi,

I am trying to integrate some hyperlinks into my workbook, essentially all they are doing is jumping from one area on a sheet to another area on the same sheet.

However, when I have a link at the top of the sheet and have it linking to a cell many rows below, when it is clicked the cell it's jumping to is made active but at the bottom of the visible area on my screen. What I mean is that the new active cell is not put at the top of the viewing area.

Is there any way of getting around this? Seems a bit stupid to me!

Cheers,

Mark.

[Dave Peterson]

How did you insert the hyperlink?

Did you use Insert|Hyperlink?

If yes, then maybe you could rightclick on the worksheet tab that holds the
hyperlink. Select view code.

Paste this in:

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error Resume Next
Application.Goto Target.SubAddress, scroll:=True
On Error GoTo 0
End Sub


mevetts wrote:
>
> Hi,
>
> I am trying to integrate some hyperlinks into my workbook, essentially
> all they are doing is jumping from one area on a sheet to another area
> on the same sheet.
>
> However, when I have a link at the top of the sheet and have it linking
> to a cell many rows below, when it is clicked the cell it's jumping to
> is made active but at the bottom of the visible area on my screen. What
> I mean is that the new active cell is not put at the top of the viewing
> area.
>
> Is there any way of getting around this? Seems a bit stupid to me!
>
> Cheers,
>
> Mark.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

Hi Dave,

I have tried pasting the code in, but when I still click the link it jumps to the linked cell, but it is still appearing at the bottom of the screen.

In web design, you use anchors and when a link is clicked that realtes to an anchor the anchor is displayed at the top of the screen, thus showing what's below it.

Any other ideas?

Thanks.

[Dave Peterson]

Did you paste the code behind the correct worksheet?
Did you enable macros when you opened that workbook?
(Try saving, closing and reopening -- answer yes to enable macros)

And you did create the link via Insert|Hyperlink, right?



mevetts wrote:
>
> Hi Dave,
>
> I have tried pasting the code in, but when I still click the link it
> jumps to the linked cell, but it is still appearing at the bottom of
> the screen.
>
> In web design, you use anchors and when a link is clicked that realtes
> to an anchor the anchor is displayed at the top of the screen, thus
> showing what's below it.
>
> Any other ideas?
>
> Thanks.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

Yep, I did all those things, but no joy.

[Dave Peterson]

I don't have another suggestion. It worked fine for me.

mevetts wrote:
>
> Yep, I did all those things, but no joy.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

Would it be possible for you to post an example so I could perhaps try and use a working model?

Or you could email me over a workbook?

Sorry, if that's a bit cheeky, but just really want to get it working.

Mark.

[Dave Peterson]

The code I posted in the previous response was all I used.

If you did what you said you did, then I don't it'll help.

mevetts wrote:
>
> Would it be possible for you to post an example so I could perhaps try
> and use a working model?
>
> Or you could email me over a workbook?
>
> Sorry, if that's a bit cheeky, but just really want to get it working.
>
> Mark.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

Have I put the code where it should be?

[Dave Peterson]

I don't see your post through excelforum, so I can't see the attachment.

did you rightclick on the worksheet tab that contained the hyperlink? Did you
select View code and paste into that code window?



mevetts wrote:
>
> Have I put the code where it should be?
>
> +-------------------------------------------------------------------+
> |Filename: screen.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=4131 |
> +-------------------------------------------------------------------+
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

Yeah, I did just as you suggested.

Here's the direct link to the screen shot -

http://www.excelforum.com/attachment...1&d=1135027929

[Dave Peterson]

Looks right to me. If Registers is the worksheet with the links.

I'm using xl2003. What version of excel do you use?

Try removing the "on error resume next" line and see what happens when you click
on one of the hyperlinks.

mevetts wrote:
>
> Yeah, I did just as you suggested.
>
> Here's the direct link to the screen shot -
>
> http://www.excelforum.com/attachment...1&d=1135027929
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[Dave Peterson]

And post what you did to create the hyperlink, too.

mevetts wrote:
>
> Yeah, I did just as you suggested.
>
> Here's the direct link to the screen shot -
>
> http://www.excelforum.com/attachment...1&d=1135027929
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

I created the hyperlink by selecting the cell and going insert - hyperlink.

I'm using 2000, could this be the difference? Will be upgrading in Jan to 2003.

Removing the line didn't help, got a bug.

[Dave Peterson]

I don't have xl2k to test.

What did you type in to the insert|hyperlink dialog?

The more info, the better the response.

mevetts wrote:
>
> I created the hyperlink by selecting the cell and going insert -
> hyperlink.
>
> I'm using 2000, could this be the difference? Will be upgrading in Jan
> to 2003.
>
> Removing the line didn't help, got a bug.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

The text was typed into the cell - 'Class 1'

I then went to insert > hyperlink.

The box popped up and I clicked the sheet from the list in the pop up box and manually typed in A50 to the cell reference box.

Then clicked ok.

[Dave Peterson]

I named a range and then linked to that named range.

Try this:

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error Resume Next
Application.Goto Application.Range(Target.SubAddress), Scroll:=True
On Error GoTo 0
End Sub

It seemed to work ok.

mevetts wrote:
>
> The text was typed into the cell - 'Class 1'
>
> I then went to insert > hyperlink.
>
> The box popped up and I clicked the sheet from the list in the pop up
> box and manually typed in A50 to the cell reference box.
>
> Then clicked ok.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[David McRitchie]

That last one with
Application.Goto Target.SubAddress, Scroll:=True
instead of
Application.Goto Application.Range(Target.SubAddress), Scroll:=True
worked for me in Excel 2002.

But it might be preferable to show cells to the left and maybe even
the row immediately above. This will attempt to show column A,
but will shift unknown cells to the right if the linked cell is not visible.
And the Back key (Alt+ArrowLt) worked for me, though I actually
use a mouse button http://www.mvps.org/dmcritchie/excel/mouse.txt

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim caddr As String
caddr = Selection.Address
On Error Resume Next
Application.Goto Reference:=Cells(Application.Max(1, _
ActiveCell.row - 1), 1), Scroll:=True
Range(caddr).Select
End Sub
---
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

"Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> I named a range and then linked to that named range.
>
> Try this:
>
> Option Explicit
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> On Error Resume Next
> Application.Goto Application.Range(Target.SubAddress), Scroll:=True
> On Error GoTo 0
> End Sub
>
> It seemed to work ok.
>
> mevetts wrote:
> >
> > The text was typed into the cell - 'Class 1'
> >
> > I then went to insert > hyperlink.
> >
> > The box popped up and I clicked the sheet from the list in the pop up
> > box and manually typed in A50 to the cell reference box.
> >
> > Then clicked ok.
> >
> > --
> > mevetts
> >
> > ------------------------------------------------------------------------
> > mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> > View this thread: http://www.excelforum.com/showthread...hreadid=494644
>
> --
>
> Dave Peterson

[David McRitchie]

Sorry worded which of Dave's worked for me Excel 2002 incorrectly.
His later one worked for me and was the one with the range.

That first one failed in Excel 2002 for me
Application.Goto Target.SubAddress, Scroll:=True
and the last one with the range worked in Excel 2002 for me
Application.Goto Application.Range(Target.SubAddress), Scroll:=True

anyway my reply had been posted with an alternative to reposition
the linked to cell with a bit more relevance to it's surrounding cells,
instead of appearing in the top left corner.

[Dave Peterson]

I could have been more clear, too.

This worked ok if I used a range name:
Application.Goto Target.SubAddress, Scroll:=True

But it didn't work if I just typed the address of the cell.

David McRitchie wrote:
>
> Sorry worded which of Dave's worked for me Excel 2002 incorrectly.
> His later one worked for me and was the one with the range.
>
> That first one failed in Excel 2002 for me
> Application.Goto Target.SubAddress, Scroll:=True
> and the last one with the range worked in Excel 2002 for me
> Application.Goto Application.Range(Target.SubAddress), Scroll:=True
>
> anyway my reply had been posted with an alternative to reposition
> the linked to cell with a bit more relevance to it's surrounding cells,
> instead of appearing in the top left corner.

--

Dave Peterson

[mevetts]

I seem to have lost the plot somwhere.

Should my next attempt involve naming a particulr range of cells and then try setting up a hyperlink to that particular range?

Then use which piece of code, as there seems to be a few versions in the thread now?

Many thanks,

Mark.

[mevetts]

I used this code provided by Dave P. -

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error Resume Next
Application.Goto Application.Range(Target.SubAddress), Scroll:=True
On Error GoTo 0
End Sub

I named the range and when the link is now clicked the range displayed begins at the top of the screen.

However, it highlights the entire range, is there any way of preventing it doing this?

Also, does this issue (ie the linked to cell being displayed at the bottom of the screen) happen in Excel 2003? As whilst I am at home at the moment and using 2000, work has 2003. So if it's not an issue in 2003, I could perhaps just wait until I'm back at work to set-up the links!

Thanks,

Mark.

[Dave Peterson]

Yes, it happens in xl2003, too.

Why not just use one cell as your hyperlink--instead of a multicell range?

mevetts wrote:
>
> I used this code provided by Dave P. -
>
> Option Explicit
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> On Error Resume Next
> Application.Goto Application.Range(Target.SubAddress), Scroll:=True
> On Error GoTo 0
> End Sub
>
> I named the range and when the link is now clicked the range displayed
> begins at the top of the screen.
>
> However, it highlights the entire range, is there any way of preventing
> it doing this?
>
> Also, does this issue (ie the linked to cell being displayed at the
> bottom of the screen) happen in Excel 2003? As whilst I am at home at
> the moment and using 2000, work has 2003. So if it's not an issue in
> 2003, I could perhaps just wait until I'm back at work to set-up the
> links!
>
> Thanks,
>
> Mark.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

That was my original problem. When I click the link, it jumps to the correct cell, but it is at the bottom of the screen, not the top. So I then have to scroll down to view the rows.

I want it so when the link is clicked the cell to which it is linked is displayed at the top of the screen.

Cheers,

Mark.

[Dave Peterson]

I selected a cell that would hold the hyperlink.

I hit ctrl-k (insert|hyperlink)
I chose "Place in this document".
I typed B923 in the "type the cell reference box"
(It was a single cell--the topleftcorner of the range--not a range of cells)
I finished up the hyperlink.

I added this to the worksheet module:

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error Resume Next
Application.Goto Application.Range(Target.SubAddress), Scroll:=True
On Error GoTo 0
End Sub

And just that single cell was selected and it was in the upper left corner of
the window.



mevetts wrote:
>
> That was my original problem. When I click the link, it jumps to the
> correct cell, but it is at the bottom of the screen, not the top. So I
> then have to scroll down to view the rows.
>
> I want it so when the link is clicked the cell to which it is linked is
> displayed at the top of the screen.
>
> Cheers,
>
> Mark.
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson

[mevetts]

A BIG thumbs up from me Dave!!! It works.

Thanks soooooo much, especially for the perseverance.

Happy Christmas!

Mark.

P.S. I'm sure I will have another little challenge in the not too distant future!

[Dave Peterson]

Whew!!

Glad you got it working.

mevetts wrote:
>
> A BIG thumbs up from me Dave!!! It works.
>
> Thanks soooooo much, especially for the perseverance.
>
> Happy Christmas!
>
> Mark.
>
> P.S. I'm sure I will have another little challenge in the not too
> distant future!
>
> --
> mevetts
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494644

--

Dave Peterson