Scroll to SheetChange..

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,840
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
 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

Code:
 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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,872
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,840
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,758
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top