VBA excel using currentRegion without selectionCha

Question

I have the following senario: whenever the user selects a certain cell, I'm copying a table from a hidden sheet to the active sheet. Then, when the user changes his selection, I need to clear the contents of the copied table and copy another table from the hidden sheet.

To copy the tables from the hidden sheet I'm using:

source.Cells(leftRow, leftCol).CurrentRegion.Copy target.Range("A1")

The problem is that this action seems to cause selectionChanged to get fired again, which triggers my ClearContents command.

Is there a way to use this command without getting selectionChanged fired?

Thanks, Li

Answer 1

Use Application.EnableEvents = False.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    '
    '~~> YOUR CODE
    '

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub