Re : Excel Workbook Events Initiated by Cut-and-Paste Operations
1. Open an Excel workbook and deploy the following event handlers in
the code module of ThisWorkbook :-
a. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Application.Enableevents = False
MsgBox "Selection Change"
[Other Code Statements]
Application.Enableevents = True
End Sub
b. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.Enableevents = False
MsgBox "Sheet Change"
[Other Code Statements]
Application.Enableevents = True
End Sub
2. By virtue of the Cut-and-Paste operations,
Workbook_SheetSelectionChange is activated, and there,
Workbook_SheetChange follows suit.
3. Subsequently, it is evident that the event of Workbook_SheetChange
is entered twice since "Sheet Change" is displayed twice
(consecutively) as well.
4. Finally, "Selection Change" is displayed at the conclusion of
the consequential events (albeit there is not repeated any selection of
a new range at all).
5. Why is it that the Workbook_SheetChange event to be entered twice,
instead of once ?
6. Is it feasible to prevent the recurrence of Workbook_SheetChange
event ? What is it that needs doing to prevent the (consecutive)
re-entry of the prevalent events (when presumably, once would suffice
thereof) ?
7. Please share your comments. Regards.
Bookmarks