Linking cells containing text from one worksheet to a cell with textin another worksheet

[lizmatthews00774]

I have a workbook with two pages, the first page has cells that
contain the main description heading of the cells on the second page.
The second page has the heading in one cell and then the breakdown of
each section under that, and repeated as there are about 11 sections.

When I paste a link or use a hyperlink on the first page, it does take
you to the heading cell on the second page, however I was wondering if
there is a way to force the cells on the bottom of the page to the top
for easier viewing. Right now it just pulls those cells up enough to
see them at the bottom of the screen, and it's hard to tell where you
should be looking. It would be so much cleaner if they automatically
were pulled up to the top so the reader could start reading without
having to scroll further.


I know this is a bad description, but now sure how else to describe.
I'm not really familiar with VB, I've muddled through it before, but
am not good with it.

thanks for any help.

 

[Billy Liddel]

Hi Liz

This seems to work. Right-click the second sheet Tab and select View Code.
Then copy the code into the module and return to the first sheet to check the
links.

Private Sub Worksheet_Activate()
ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

Peter Atherton

 

[lizmatthews00774]

Thanks Billy!

That worked perfectly. I do have one other question and I'm not sure
if there is a solution.
When you click away from the second sheet to another sheet, is there a
way to have it automatically return to the top of the page. That way
if a user decides to skip the matrix on the first page to navigate and
just clicks to the appropriate tab, it will be at the top of the page
to scroll down through, even if they have previously viewed it through
the links on the first sheet. Does that make sense?

 

[lizmatthews00774]

Thanks Billy!

That worked perfectly. I do have one other question and I'm not sure
if there is a solution.
When you click away from the second sheet to another sheet, is there a
way to have it automatically return to the top of the page. That way
if a user decides to skip the matrix on the first page to navigate and
just clicks to the appropriate tab, it will be at the top of the page
to scroll down through, even if they have previously viewed it through
the links on the first sheet. Does that make sense?

Bump - anyone?

 

[Gord Dibben]

See Peter Atherton's reply.


Gord Dibben MS Excel MVP

 

[Billy Liddel]

Liz
Sorry for the delay!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Index = 2 Then
Exit Sub
Else
Range("A2").Select
End If
End Sub

This assumes that the other macro went in sheet 2 so it exits if that is the
case. in all other sheets A2 is selected. Change to suit also the range if
you are not happy with that.

The code goes in the Workbook Module. Rightclick the Excel Icon on the File
Menu, choose View code and copy into the module.

Regards
Peter Atherton

 

[lizmatthews00774]

Liz
Sorry for the delay!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Index = 2 Then
  Exit Sub
  Else
  Range("A2").Select
End If
End Sub

This assumes that the other macro went in sheet 2 so it exits if that is the
case. in all other sheets A2 is selected. Change to suit also the range if
you are not happy with that.

The code goes in the Workbook Module. Rightclick the Excel Icon on the File
Menu, choose View code and copy into the module.

Regards
Peter Atherton






- Show quoted text -

Thanks again, I'm not sure I'm using the code properly though.
So I have a workbook that has 9 sheets in total. The first is an
overview, the second contains a matrix that links to the last 7
sheets.
The matrix looks like this approx:


a b c d e f g
1x x x x x x x
2x x x x x x x
3x x x x x x x
4x x x x x x x
5x x x x x x x
6x x x x x x x
7x x x x x x x
8x x x x x x x
9x x x x x x x
10x x x x x x x
11x x x x x x x
12x x x x x x x

and when you click on an x it takes you to let's say, the worksheet
for c, and the cell that has the info for 12, which thanks to the
previous code you gave me appears at the top of the page. I've entered
that code on the sheets for a, b, c, d, e, f and g.

I entered in the second code you gave me under the first code and if I
go to the matrix, click on X for c12, it takes me there. Then I click
to another tab and decide I want to go back to sheet C and read
through all the information for 1-12 by scrolling myself, it's still
showing c12 information and hasn't reset to the start of the sheet,
which is what I'd like it to do once you click away from that page.

Should I be deleting the first code and just use the second, or should
I be putting the second code somewhere other than a, b, c, d, e, f and
g?


long and confusing, but like I said before - I'm not the greatest with
this stuff.

 

[Billy Liddel]

containing t..."

Post In Discussion Group microsoft.public.excel
Please select a Discussion Group.



Subject

Please include a Subject that is no more than 70 characters long.
Message

Well Liz the second code went in the Workbook Module. See the previous post
for details.
However, I'm afraid there is a conflict with what you are asking. I had
assumed that the links where all on the second sheet and you wanted to go to
A2 on each other sheet when you clicked on a tab on any other sheet. The code
worked for that but AFAIK the Worksheet_Activate takes precedence over the
followHyperlink so each sheet will go to A2

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

I guess that the above code is more correct but perhaps you could post that
together with the worksheet code on the Program groups and see if anyone can
resolve the conflict.

Best of luck, if you decide to post the query perhaps you could let me know
at peter_athertonAtHotmail.com

Do the obvious with the AT

Regards
Peter

 

[Billy Liddel]

got it!

Enter this into the Workbook Module. Right-click Excel Icon left of File
Menu and paste code.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Range("A2").Select
End Sub

Copy this to each destination sheet code (where the link is)

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

Regards
Peter Atherton
When you double click on any sheet this will take you to A2 at any time and
will not override the following hyperlink code.