Run Macro when user clicks Paste or Paste Special

[Bill Bisco]

Dear all,

I am currently using this code to run a macro when a user types V

Application.OnKey "^v", "PasteSizedPicture"

I'd like to run the same Macro "PasteSizedPicture" whenever a user clicks Paste or Paste Special instead of the regular action. Is there a method to do this?

Thanks,

Bill

 

[VeryForgetful]

You could try this code in the worksheet module assuming you know which column the paste will be made to, in this example column A

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 1 Then Call PasteSizedPicture


End Sub

 

[ask2tsp]

Paste this code in the codepage of the worksheet (not in a module)

Private Sub Worksheet_Change(ByVal Target As Range)
    'English
    'Const UNDOCMD As String = "&Undo"
    'Const PASTECMD As String = "Paste"
    'Const PASTECMD2 As String = "Paste" '<-- change!
    
    'Dutch
    Const UNDOCMD As String = "Onge&daan maken"
    Const PASTECMD As String = "Plakken"
    Const PASTECMD2 As String = "Opmaak van"
    
    Dim cb      As CommandBars
    Dim cbco    As CommandBarControl
    Dim lastAction As String
    Dim i As Long
    
    ' Get the last action performed by user
    Set cb = Application.CommandBars
    Set cbco = cb("Standard").Controls(UNDOCMD)
    
    If cbco.Enabled Then  'is a command to undo available?
        lastAction = cbco.List(1)
        ' Check if the last action was a paste
        If Left(lastAction, Len(PASTECMD)) = PASTECMD _
        Or Left(lastAction, Len(PASTECMD2)) = PASTECMD2 Then
            PasteSizedPicture
        End If
    End If
End Sub

I work with a dutch version of excel. If you use the english version comment the dutch const lines, uncomment the english const lines and figure out what text the PASTECMD2 looks like (probably something like "Keep format", mouse over the various paste options and look at the text)