VBA : Intercepter un coller - Paste Event

Introduction

L'action de coller sur une feuille ne constitue pas un événement pour Excel. Nous allons voir qu'il est possible toutefois de simuler cet événement.

Méthode

Que se passe t'il lorsque l'utilisateur effectue une action de Collage?
Quel que soit le type de collé : Collé, Collage spécial, etc..., cette action déclenche deux choses :

• une modification dans la feuille (des données nouvelles y sont insérées)
• un ajout dans la liste des actions possibles d'être annulées (Undo)

Toute modification dans une feuille de calcul Excel déclenche l'événement Workbook_SheetChange. On peut donc intercepter le coller grâce à cet événement.

[on se place ici au niveau Classeur pour contrôler le coller dans tout ce classeur. Il est possible de ne le faire que sur une unique feuille en utilisant son événement Worksheet_Change]

Code

Ce code utilise un événement du classeur. Il convient donc de le placer dans le module ThisWorkbook.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Lors de tout changement dans les feuilles du classeur
Dim C As String, I As Long
    'on vérifie dans la barre d'outils 
    With Application.CommandBars("Standard")
        I = .FindControl(ID:=128).Index
        la dernière action qu'il est possible d'annuler
        C = .Controls(I).List(1)
    End With
    's'il s'agit d'un Coller ==> on lance notre procédure
    If C = "Coller" Then maMacro
End Sub

Exemple d'utilisation

Dans cet exemple, l'utilisateur souhaite intercepter le coller, l'annuler, et récupérer le contenu du presse-papier pour le modifier (changer des lignes, des colonnes, etc...).

Attention : Cette procédure nécessite de cocher la référence "Microsoft Forms 2.0 Object Library." (pour le dataObject)
Pour cela, sous VBE : Outils/Références...

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim C As String, I As Long
    With Application.CommandBars("Standard")
        I = .FindControl(ID:=128).Index
        C = .Controls(I).List(1)
    End With
    MsgBox C
    If C = "Coller" Then maMacro
End Sub


Sub maMacro()
Dim Presse_Papier As String
    'on annule le coller
    With Application
    'on désactive les événements sinon le Undo va déclencher à nouveau SheetChange
        .EnableEvents = False
        .Undo
    'réactivation des événements
        .EnableEvents = True
    End With
    'on récupère le contenu du presse-papier
    Presse_Papier = GetPressPapier
    'Ici l'action à réaliser
    MsgBox Presse_Papier
End Sub

Function GetPressPapier() As String
'Source : http://excel.developpez.com/faq/?page=PressePapier#RecupPressePapier
'nécessite d'activer la référence "Microsoft Forms 2.0 Object Library."
 
    With New dataObject
        .GetFromClipboard
        GetPressPapier = .GetText(1)
    End With
End Function

A noter : comme on désactive le gestionnaire d'événement, il est possible qu'en cas de plantage ce dernier ne se réactive pas.
Pour pallier à cela, il convient de placer dans un module standard cette macro :

Sub SOS()
    Application.EnableEvents = True
End Sub

Conclusion

Cette astuce a pour source cette discussion du forum.
Elle permet également d'intercepter d'autres actions de l'utilisateur, notamment "Insérer des cellules".
Si vous désirez connaitre les noms exacts contenus dans la liste des actions à annuler (Undo), vous pouvez utiliser ce code :

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim C As String, I As Long
    With Application.CommandBars("Standard")
        I = .FindControl(ID:=128).Index
        C = .Controls(I).List(1)
    End With
    MsgBox C
End Sub

On ne pourra cependant pas, par cette méthode, intercepter les actions qui ne modifient pas la feuille, par exemple : Copier...