The automatic selection selection event that follows the cell edit selects the same new cell, even if my change event code defines a new destination cell

Question

Screen suppression is canceled and the original macro ends

I spent 3 hours on this and tracked the error? at this point, but it’s illogical for me that I see what is happening, and therefore I do not find the optimal treatment. Please, help.

I am using the _SelectionChange event to store cell values ​​in a global variable. Used as the old value when the _Change event is fired after a cell value has changed.

As part of the _Change-initiated macro, the values ​​of the list are updated dynamically, and towards the end I run Sort to sort the list again. I complete it by reactivating the cell that was changed before sorting (in its new position).

I kept track of the value stored in oVal (change of choice) and it displays the correct value all the time. However, when the macro ends, 1 additional eventChange event is automatically added (without changes it really changes), which gives a new value oVal and ruins the subsequent calculations.

I noticed that, step by step, I decided that the selected cell was selected during sorting, but I intentionally activated the cell that I wanted to finish at the end of my macro. (and the IS value is updated), however, after the macro ends, the sort selection change is canceled.

: , Anne - 1 - 2 - 3 - 4

1. (oVal : 1) 4. oVal1 nVal5, John 1, Stuart 2, Bono3; Anne 4. Sort Anne . " " , 4 . oVal 4 ( ) . , , SelectionChange oVal 2 ( ) - !!!! . , , Ann (, Spinner), - oVal.

EDIT: MCV. TestScene Excel:

(H11 oVal )

TestCode :

Option Explicit

Dim oVal As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oVal = Target.Value
Application.EnableEvents = False
ActiveSheet.Range("H11").Value = oVal
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("F13").Value = 1
Range("F14").Value = 2
Range("F15").Value = 3
Range("F16").Value = 4
Call testsort
Application.EnableEvents = True
Range("F16").Activate
End Sub

Sub testsort()
    ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table2").Sort.SortFields.Add _
        Key:=Range("Table2[[#All],[c]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub 

, , , oVal 5 , . val 2.

: (-removed -)

2. : . .

---

:

Option Explicit

Dim oVal As Variant
Dim cameFromChange As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If cameFromChange = False Then
    oVal = Target.Value
    Application.EnableEvents = False
    ActiveSheet.Range("H11").Value = oVal
    Application.EnableEvents = True
Else
    cameFromChange = False

    Application.EnableEvents = False
    Range("F13").Value = 1
    Range("F14").Value = 2
    Range("F15").Value = 3
    Range("F16").Value = 4
    Call testsort
    Application.EnableEvents = True
    Range("F16").Activate
End If


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
cameFromChange = True

End Sub

Answer 1

selection_change, .

Excel ( - - Enter move selection), , , selection_change. , - selection_change .

, :

• .
• ( , Selection)
• Worksheet_Change
• selection_change

, ( , , ), selection_change, , , Worksheet_Change ( , "" Value),.

Answer 2

, .

Public Sub appTGGL(Optional bTGGL As Boolean = True)
    Application.ScreenUpdating = bTGGL
    Application.EnableEvents = bTGGL
    Application.DisplayAlerts = bTGGL
    Application.Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
    Debug.Print Timer
End Sub

:

sub main()
    apptggl btggl = false
    'all of your code
    apptggl
end sub

fron .