Hyperlink question

[Michelle York]

I just sent another post titled " Problem with Insert "Name-Define" and
remembered that I had another question related to hyperlinks. I think that
I know the answer to this, but I could very easily be wrong.

When I hit the hyperlink to take me to the other worksheet, I would REALLY
like for the 2nd worksheet to appear with the linked cell at the very top of
the page. For example, when I link to the level "A" books, I want the
hyperlinked cell that starts the A's to be at the very top of the page, so
that I have an entire page of A books to see before I have to scroll down
again.

The way it is now, sometimes I'll link and the appropriate cell will be near
the top, sometimes it's in the middle of the page, and sometimes it's at the
very bottom which requires an immediate scroll to see more of the "A" books.

I know that this is a very minor issue--not a huge problem, just a minor
inconvenience. But it's possible that there's a solution out there
somewhere. I THINK that I've even tried to watch where the cell is when I
define it. So that when I define it, it's already located at the top. That
hasn't worked.

Any ideas or is this just a function that Excel is not capable of?

 

[Ken Johnson]

Hi Michelle,
You might not like this solution, which involves using a worksheet
event procedure.

If you decide to use this it means that your workbook's security level
will have to be set to medium and everytime the workbook is opened the
"Security Warning" dialog will appear and the user will have to decide
whether to click "Disable Macros" or "Enable Macros".

If there are no problems with this then...

1. Copy this code...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

2. Right click the sheet tab of the sheet with the hyperlinks (the
first sheet) then select "View Code" from the popup menu.

3. Paste the code into the blank code module that appears.

4. Save, then press Alt + F11 or go File|Close and Return to MIcrosoft
Excel to return to the Normal Excel interface.

5. If you haven't already changed the Security level then go
Tools|Macro|Security... then change to medium. Then for the new
security level to take effect close the workbooik then reopen.

6. If you choose "Enable Macros" on the "Security Warning" dialog then
the Code you pasted will work and after clicking a hyperlink the
selected cell on sheet2 will be at the top of the sheet.

Ken Johnson