Hyperlinks

[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

 

[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]

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

 

[Dave Peterson]

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

 

[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]

Have I put the code where it should be

+-------------------------------------------------------------------
|Filename: screen.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4131
+-------------------------------------------------------------------

 

[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]

Yeah, I did just as you suggested.

Here's the direct link to the screen shot -

http://www.excelforum.com/attachment.php?attachmentid=4131&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

 

[Dave Peterson]

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

 

[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]

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

 

[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

 

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