Stop Active Cell from Firing Event after Hitting Return

[Excel Monkey]

I have two events in a class module. One is a App_SheetSelectionChange event
and the other is a App_SheetChange Event. When the user clicks on a cell the
App_SheetSelectionChange event is fired. If the user enter data into the
cell, the App_SheetChange Event is fired.

However as with the regular settings in Excel, after the second event is
fired, and the selected cell transitions down to the cell below the target
(the natural movement of the selected cell afte the user hits Return), the
App_SheetSelectionChange event fires again.


How do I stop this from happening? I know I can change the settings in
Excel to stop the active cell from moving down after Return is hit. However,
I do not want to do this. Puttiing Application.EnableEvents = False at the
end of the App_SheetChange Event will disable all events and then my
App_SheetSelectionChange will not fire at all. How do you get around this?

Private Sub App_SheetSelectionChange(ByVal sh As Object, ByVal Target As
Range)

End Sub

Private Sub App_SheetChange(ByVal sh As Object, ByVal Target As Range)
With Application
..ScreenUpdating = False
..EnableEvents = False
..Calculation = xlCalculationManual
End With

'Call other subs

With Application
..ScreenUpdating = True
..EnableEvents = True
..Calculation = xlCalculationAutomatic
End With

End Sub


Thanks

EM

 

[Per Jessen]

Hi EM

Set up a boolean variable, wich is set True when the Change event fire, and
check if the variable is true or not when the SelectionChange event is
fired. Untested!

Dim ChEvent As Boolean

Private Sub App_SheetSelectionChange(ByVal sh As Object, ByVal Target As
Range)
If Not ChEvent = True Then
'Your code

End If
ChEvent = False
End Sub

Private Sub App_SheetChange(ByVal sh As Object, ByVal Target As Range)
ChEvent = True

With Application
..ScreenUpdating = False
..EnableEvents = False
..Calculation = xlCalculationManual
End With

'Call other subs

With Application
..ScreenUpdating = True
..EnableEvents = True
..Calculation = xlCalculationAutomatic
End With
End Sub

Hopes this helps