scroll linked cell to upper left position

[aileron44]

In sheet 1, I link to a cell in sheet 2. I want the linked cell in sheet 2 to scroll to upper left of sheet 2. I need to do this from different links in worksheet 1 to different cells in sheet 2.

Suggestions or solutions?

Thanks,
George

[mrice]

Try pasting this code into the Sheet2 tab in the VBA editor (Alt F11).

Private Sub Worksheet_Activate()
MyRow = Selection.Row
MyColumn = Selection.Column
Cells(1, 1).Select
ActiveWindow.SmallScroll ToRight:=MyColumn - 1
ActiveWindow.SmallScroll Down:=MyRow - 1
End Sub

[snb]

I'd prefer:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
 Application.Goto Evaluate(Target.SubAddress), True
End Sub

In the codemodule of sheet1

[aileron44]

I'd prefer:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
 Application.Goto Evaluate(Target.SubAddress), True
End Sub

In the codemodule of sheet1

I tried this code in both code modules of the Workbook and Sheet1 but it doesn't work. I've also tried creating a module and putting it in there but no luck. I'm trying anything at this point. I've tried several other pieces of code with no luck. I'm probably putting it in the wrong place. If that's the case, can someone tell show me where it goes. I even downloaded a spreadsheet that has this code init and it worked at first but then stopped working. I have no idea why?

I've attached a sample spreadsheet of what I'm trying to do.

Please help!

Thanks,
George

[aileron44]

Try pasting this code into the Sheet2 tab in the VBA editor (Alt F11).

Private Sub Worksheet_Activate()
MyRow = Selection.Row
MyColumn = Selection.Column
Cells(1, 1).Select
ActiveWindow.SmallScroll ToRight:=MyColumn - 1
ActiveWindow.SmallScroll Down:=MyRow - 1
End Sub

I tried this code in sheet2 but I got a compile error for undefined variable "MyRow."

I'm not having a good day.

George

[snb]

The code has to be put into the macromodule of the sheet that contains the hyperlink.

See the attachment.

[aileron44]

The code has to be put into the macromodule of the sheet that contains the hyperlink.

See the attachment.

HI SNB,

Your example works perfectly and does exactly what I need. However, I copied your code into a new blank workbook and it worked. I'm using Excel 2007.

When I tried to save it, Excel wants to save it as an .xlsm (macro enabled) because of VB Project. After I save it as this, it stops working. I then saved it as .xlsx but it didn't work either. I saved it as .xls and it didn't work.

Is there something wrong in my copy of Excel or do I have something selected that causes it to misfire?

George

[snb]

Please do not quote (see the forum rules).

Nothing wrong with your copy of Excel.
I'd say: dive into the code, try to understand it, dive into the VBeditor, use every debugging tool possible and merge into the vbeditor's helpfiles. It'll make you more independent of this forum.