Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Active Cell from Firing Event after Hitting Return
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Active Cell from Firing Event after Hitting Return
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 --- Per "Excel Monkey" skrev i meddelelsen ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Event not firing | Excel Programming | |||
Click event on menu item is lost after first time firing of the event | Excel Programming | |||
Stop Worksheet_SelectionChange event from firing? | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |