Hyperlinks to show on top of page

[Elisabeth]

In Excel I have used Name Manager for a range of cells, which are used for
hyperlinks. When hyperlink is activated the range of cells appear at the
bottom of the page. How do I make the hyperlink show the cells at the top of
the page?

 

[JE McGimpsey]

In Excel I have used Name Manager for a range of cells, which are used for
hyperlinks. When hyperlink is activated the range of cells appear at the
bottom of the page. How do I make the hyperlink show the cells at the top of
the page?

I'm not sure what you mean by "the range of cells appear at the bottom
of the page".

What type of hyperlink are you entering?

 

[Elisabeth]

Hi JE,

I am doing a publications catalogue listing books according to categories
and books details. I select a range of cells which I name, using name
manager, ie for name ref192671729 the value is "Dermatology in Chinese
Medicine" and it refers to ='Publications Review'!$A$151:$E$155. Then in the
index I select the book and I hyperlink it to a place in that document with
that reference 192671729. When I then click on this title in the index it
takes me to the bottom of the screen. Is there any way of making it appear at
the top of the screen?

 

[CyberTaz]

Do you mean that the target cells are coming into the viewing area but not
into the upper left corner? If so, that's something which is not easily - if
at all - within your control. I'm not even sure it can be done with VBA, but
John would be better able to address that.

However, you've still not indicated what version of Excel you're using. Even
though it "may" not matter in this case it's something that should always be
disclosed when posting

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Elisabeth]

Hi Bob,

Yes this is exactly what I mean, I need the text to appear on top left
corner of screen. I am using Excel 2007 for PC.

Please could you kindly forward my request to John with the hope that he
might be able to help. There should be a small symbol to insert in such a
formula!

Many thanks and best regards,

Elisabeth

 

[CyberTaz]

John will see it here, although this is the Mac Excel group. He may have
something to suggest as he works both sides, but you may be better of
posting to one of the PC Excel groups from the following kink:

http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx

My understanding is that MS can't do this because of patents/copyrights held
by a competitor... But I don't know that to be a fact

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

John will see it here, although this is the Mac Excel group. He may have
something to suggest as he works both sides, but you may be better of
posting to one of the PC Excel groups from the following kink:

http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx

My understanding is that MS can't do this because of patents/copyrights held
by a competitor... But I don't know that to be a fact

Unfortunately, I don't know of anything that will change XL's native
hyperlink behavior. Certainly in versions other than XL08, one could
come up with event macro-driven solutions.

For example, one could put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
gbSwitchedSheets = ActiveSheet.Name <> "Publications Review"
End Sub

Put this in the 'Publications Review' worksheet code module:

Private Sub Worksheet_Activate()
Application.OnTime _
EarliestTIme:=Now() + TimeSerial(0, 0, 1), _
Procedure:="DisableAutoScroll"
End Sub

Private Sub Worksheet_Deactivate()
gbSwitchedSheets = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If gbSwitchedSheets Then
ActiveWindow.ScrollRow = ActiveCell.Row
gbSwitchedSheets = False
End If
End Sub

And put this in a regular code module:

Public gbSwitchedSheets As Boolean

Public Sub DisableAutoScroll()
gbSwitchedSheets = False
End Sub

The work is done in the Worksheet_SelectionChange() routine. Everything
else is to prevent scrolling when the sheet is entered via, say,
clicking on the worksheet tab.