Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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" wrote in message ...
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

Hyperlinks
 

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.

Hyperlinks
 

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

Hyperlinks
 

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


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=494644

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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

Hyperlinks
 

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