Scroll to SheetChange..

[omairhe]

Hey everyone,

I would appreciate some help on using the sheet change event with scrolling. For instance, how would I Scroll to the target cell that triggered sheet change event.

One way to achieve is to use the following two lines

 ActiveWindow.ScrollRow = Selection.Row
 ActiveWindow.ScrollColumn = Selection.Column

But on pressing enter over target cell upon change. it will scroll aswell which requires me to insert one more line like this

 ActiveWindow.ScrollRow = Selection.Row
 ActiveWindow.ScrollColumn = Selection.Column
 ActiveWindow.SmallScroll Down:=-1

But what if the user used a down arrow key or up or left or right or tab key.

ActiveWindow.SmallScroll Down:=-1 this will work only with Enter key.


Help will be appreciated
thank you

 

[mikerickson]

Have you tried

Application.Goto Target

 

[omairhe]

Have you tried

Application.Goto Target

I have tried but did not work..

 

[My Aswer Is This]

If you click on B45 to make a change to cell B45 why do you need a script to scroll to B45?
Your already at B45

Or am I missing something.

 

[omairhe]

If you click on B45 to make a change to cell B45 why do you need a script to scroll to B45?
Your already at B45

Or am I missing something.

You need to try this code in the workbook category

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
 Application.ScreenUpdating = False
 ActiveWindow.ScrollRow = Selection.Row
 ActiveWindow.ScrollColumn = Selection.Column
 ActiveWindow.SmallScroll Down:=-1
 Application.ScreenUpdating = True
End Sub

Before you do also FreezePane both column and row on cell G2 . Now enter values in column H in descending order. Note that my data has alphabetical order of items in column A.

There are three advantages .
1. Your recent entry will appear on top.
2. You can recheck what you typed against value in column A as it will be at the top and easily viewable.
3. You make more room for yourself on the screen each time you enter a new value.