[ad_1]
is there a way to “listen’ to the PasteSpecial command and its parameters in excel?
I’m writing a vba code to manage the Range.PasteSpecial method and realized I need a way to know if the Transpose parameter of the action defined by the user is true or false, so I can treat the source data accordingly.
Offcourse, since I don’t know when Transpose is TRUE, this piece of code below does not really work.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UndoList As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Application.CommandBars("Standard").Controls("&Undo").Enabled = True Then
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
MsgBox (UndoList & "!")
If Left(UndoList, 5) = "Paste " Or UndoList = "Paste special" Then
If UndoList = "Paste special" Then
If Transpose Then
MsgBox ("Paste Transposed Values")
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
'and other stuff
Exit Sub
Else
MsgBox ("Paste Values")
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'and other stuff
Exit Sub
End If
ElseIf Left(UndoList, 5) = "Paste" Then
MsgBox ("Just Paste formats")
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False
Exit Sub
Else
MsgBox ("choose another method")
'Application.Undo
Exit Sub
End If
Else
Exit Sub
End If
UndoList = ""
End If
End Sub
[ad_2]