Auto sheet scroll sub

[Max]

I've frozen panes at B2. In the frozen cell A1, I would like to input a cell
ref between B2:IA5000 (say), and have the sheet auto-scroll to position the
cell ref input in A1 as the top left cell in "B2" (bring it into focus)

For example, if I input in A1: IA500, the sheet should scroll such that
IA500 is positioned exactly over "B2". If for some reason it is not possible
for the sheet to scroll as desired, then the nearest scroll fit to "B2" will
do. Is there a sub which could accomplish this ? Thanks.

 

[NickHK]

Try this. You should add some error handling for invalid addresses :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address=Range("A1").Address Then
Application.Goto Range(Target.Value), True
End If
End Sub

NickHK

 

[Max]

Nick, thanks!
Works fine.

 

[Max]

.. You should add some error handling for invalid addresses :

I'm having difficulty framing this up, for the sub to exit if A1 is cleared
or contain invalid address. Any sample code at hand which could be used here?
Thanks.

 

[NickHK]

Max,
A simple way is to ignore the error :

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address=Range("A1").Address Then
Application.Goto Range(Target.Value), True
End If
End Sub

But you user may be confused as to why nothing appears to happen. So you
could provide some info:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Address = Range("A1").Address Then
Application.Goto Range(Target.Value), True
End If
Exit Sub
Handler:
Dim msg As String
Select Case Err.Number
Case 1004
msg = "Probably invalid address"
Case Else
msg = "Unknown error'"
End Select
MsgBox msg
End Sub

NickHK

 

[Max]

Great! Thanks for the examples, Nick.