Stop automatic scroll down! After any change, excel scrolls down... I have to scroll up everytime for new input...

Question

Microsoft Excel 365

version 16.73

I want ant Excel to stop scrolling down each time i perform any change.

When I want to perform multiple changes or various cell inputs i have to scroll back up every time.

This is my first time using VBA, it's an auto order table code (as a value changes inside the table, it automatically orders it) must have messed up along the process. The cells in this table refer to other cells inside the same sheet.

Never happened before, guessing why the VBA code fault or some settings related.

thanks

Code:

Worksheet-------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
ORDENAR
End Sub

Module----------------------------

Sub ORDENAR()
'
' ORDENAR Macro
'

'
ActiveWorkbook.Worksheets("INV.").ListObjects("Table1").SORT.SortFields.Clear
ActiveWorkbook.Worksheets("INV.").ListObjects("Table1").SORT.SortFields.Add2 _
Key:=Range("Table1[Monto]"), SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("INV.").ListObjects("Table1").SORT
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub••••ˇˇˇˇ

Answer 1

Hi Ludovic C!

To prevent Excel from automatically scrolling down after each change, you can modify your VBA code as follows:

Worksheet Code:

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' Disable event handling temporarily ORDENAR Application.EnableEvents = True ' Enable event handling End Sub

Module Code: Sub ORDENAR() Application.ScreenUpdating = False ' Disable screen updating temporarily

With ActiveWorkbook.Worksheets("INV."). ListObjects("Table1"). Sort . SortFields.Clear . SortFields.Add2 Key:=Range("Table1[Monto]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal . Header = xlYes . MatchCase = False . Orientation = xlTopToBottom . SortMethod = xlPinYin . Apply End With

Application.ScreenUpdating = True ' Enable screen updating End Sub

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru

Answer 2

Hi Shakiru,

Works beautiful!

Put in a nice way too, as I can read and almost understand the code! haha

Tank you so much!

NICE