Search code examples
excelvbacopy-pastepasteworksheet-function

Activation event getting triggered by PasteSpecial operation

While programming a module in VBA that operates on copy-pasting a range based on formulae on a sheet, the currently activated sheet's Worksheet_Activate event is triggered.

I know that I can use Application.EnableEvents = False to disable the trigger, however, I would like to understand why the PasteSpecial operation for a Range object triggers the Worksheet_Activate method, as I cannot find any mention of the same on the Range.PasteSpecial documentation, or what actions trigger the Worksheet_Activate event.

For a reproducible example, please create a workbook with two sheets, Sheet1 and Sheet2, and insert code in Sheet1 as so:

Public Sub Worksheet_Activate()

    MsgBox "Sheet 1 has been activated."

End Sub

Add a separate module with code as so:

Public Sub copy_and_paste_in_sheet2()

    Set Rng1 = ThisWorkbook.Worksheets("Sheet2").Range("A1:A10")
    Rng1.Copy

    Set rng2 = ThisWorkbook.Worksheets("Sheet2").Range("B2:B12")
    rng2.PasteSpecial xlPasteFormulas

End Sub

While Sheet1 is activated, run the copy_and_paste_in_sheet2 macro, and it will be evident that the Worksheet_Activate event for Sheet1 is triggered despite no apparent code in the second module explicitly doing so.

I would expect that notwithstanding any use of Select operations that require the Activate command to work, the PasteSpecial operation should not ideally trigger the Worksheet_Activate event. Could you please direct me to the relevant documentation for this behaviour?

Solution

  • It seems to be one of the odd things you may have when dealing with Excel: PasteSpecial quickly "activates" the sheet you are pasting to. You can see the screen flicker (at least I can, using a VM that is rather slow when it comes to screen updating). Also, if you put similar event code to sheet2, you will see that it is triggered also.

    The strange thing is that in the triggered sheet you see still sheet1 as ActiveSheet. So the following code (as event routine of Sheet2) will print "activate 2 Sheet1"

    Public Sub Worksheet_Activate()
     Debug.Print "activate 2 " & ActiveSheet.Name
End Sub

    I doubt that you will find any documentation for that behaviour. And your best bet is to accept this and (as you already wrote) use Application.EnableEvents = False