scrolling target cell to top of page after hyperlink

[BROCK8292]

i am now trying to get the target cells of my hyperlinks to appear at the top
of the page instead of at the bottom. i know you answered this one in bill
elerdings post "execute macro after hyperlink" but i cant get it to work,
maybe because i wrote my hyperlink formulas instead of inserting them into
the cells.
is there anything else you can think of
thank you for your continued assistance
brock

 

[Gary''s Student]

paste this small macro in the worksheet code area:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Goto Reference:=ActiveCell.Address(ReferenceStyle:=xlR1C1),
Scroll:=True
End Sub

be careful of the wrap

 

[BROCK8292]

thanks for the response garys student
i tried pasting it in the code of either spreadsheet but neither worked
do you have any other ideas
thank you
brock

 

[Gary''s Student]

Let's make sure the routine is in the correct area. It needs to go in the
worksheet code area:


It is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[BROCK8292]

normally i just go to tools>macro>vba then select the worksheet and go from
there.
im familiar with basic macros but have difficulty with the language when
trying to write my own. i tried doing it like you said but still cant get it
to work
perhaps i am explaining it incorrectly
i have 1000 hyperlinks in one spreadsheet whose targets are 1000
corresponding rows in a sheet 2. i then used a row adder macro to seperate
these corresponding rows by 49 rows each. when i use the hyperlinks, the
target row appears at the bottom of the screen and id like for it to appear
at the top of the screen. does that make sense?
im pretty new to this and i know i dont express my ideas as well as many of
the other users do
thank you for your patience and continued assistance
brock

 

[Gary''s Student]

Our patience will be rewarded in the end.

I think we should start with the simplest possible example, get it to work
on that example, and then migrate to your final worksheet. That way we will
know that your version of Excel and its configuration are consistent with
this approach.

1. start with a fresh, brand new, worksheet.
2. select A1 in Sheet1 and pull-down:
a. Insert > Hyperlink > Place in this document > B9
3. click the link to insure we get to B9
4. right-click the tab (sheet name) at the bottom and select View code
5. Paste this in:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim s As String
MsgBox ("re-scrolling")
s = ActiveCell.Address(ReferenceStyle:=xlR1C1)
Application.Goto Reference:=s, Scroll:=True
End Sub

6. Close the VBA window
7. Click the link and we should observe two things:
a. A message box will pop up – click o.k.
b. The jump and re-scroll should occur

 

[BROCK8292]

yes, this example worked for me
but i inserted the hyperlink into this spreadsheet
perhaps the reason it wont work on my other spreadsheet is because i
typed the hyperlink formula into the document so that i could copy it down
the page
perhaps if i showed you the formula im using it could help
my hyperlink formula reads
=hyperlink("#"&cell("address",sheet1!a1)"stk"&rept("o",3-len(row()))&row())
i thank you for your time and perseverance
brock
spokane, wa