How can I catch a Paste action and default it to PasteSpecial-Values

[srinu1264]

Hi ,

How can I catch a Paste action behaviour(Paste menu click and ^v) and
default it to PasteSpecial-Values.

I should be able to catch these events and call my custom subroutine to
handle the situations.

thanks
Vasu

 

[Tom Ogilvy]

There is no direct support for it in Excel VBA or its event model.

You might be able to kludge something together with change and
selectionchange events, checking the value of application.CutCopyMode.

 

[Ardus Petus]

Here is one solution, catching all possibilities of Pasting.
To enable special pasting, run SetMyPaste
To reset to normal pasting, run: ResetMyPaste

HTH
--
AP

'------------------------
Option Explicit

Sub SetMyPaste()
SetAllPaste (True)
End Sub

Sub ResetMyPaste()
SetAllPaste (False)
End Sub

Private Sub SetAllPaste(bSet As Boolean)
Dim aCb As Variant
Dim iCb As Integer
aCb = Array("Standard", "Edit", "Cell")
For iCb = LBound(aCb) To UBound(aCb)
setPaste aCb(iCb), bSet
Next iCb
If bSet Then
Application.OnKey "^v", "MyPaste"
Else
Application.OnKey "^v"
End If
End Sub

Private Sub setPaste(sCb As Variant, bSet As Boolean)
Const iIdPaste = 22
Dim cbcControl As CommandBarControl
For Each cbcControl In CommandBars(sCb).Controls
With cbcControl
If .ID = iIdPaste Then
If bSet Then
.OnAction = "MyPaste"
Else
.Reset
End If
Exit For
End If
End With
Next cbcControl
End Sub

Private Sub MyPaste()
If Application.CutCopyMode Then
Selection.PasteSpecial Paste:=xlPasteValues
End If
End Sub
'-------------------------------

"srinu1264" <[email protected]> a écrit
dans le message de (e-mail address removed)...

 

[broro183]

hi Vasu,

This is just an fyi as it looks like Ardus has provided what you were
after.

[alt + t + c], choose Commands tab & "Edit" category & you can drag the
"paste values" button onto a toolbar & then use this. Problem is that
this only works if the user wants to play ball!
Of course, education of the user wrt this option could be used in
conjunction with Ardus's option.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Peter T]

Not sure if any use but you can catch the paste menu click before it does
the paste

' normal module

Dim clsPasteEvnt As New Class1 ' suggest rename Class1

Sub SetPasteEvent()
'call from say Workbook_Activate and/or an Open event

Set clsPasteEvnt.ctrPaste = Application.CommandBars.FindControl(ID:=22)
End Sub

' in a class named Class1
' this won't work in XL97
Public WithEvents ctrPaste As Office.CommandBarButton

Private Sub ctrPaste_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)

If TypeName(Selection) = "Range" Then

Debug.Print Selection.Address

End If
End Sub

Maybe one of the Clipboard API gurus can catch the clipboard contents in
this event and/or with Ardus' suggestion (Ctrl-V ?).

Regards,
Peter T

 

[Peter T]

A bit more to play with -

Public WithEvents ctrPaste As Office.CommandBarButton

Private Sub ctrPaste_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
Dim vaFmts, vFrmt

vaFmts = Application.ClipboardFormats

If TypeName(Selection) = "Range" Then
For Each vFrmt In vaFmts
If vFrmt = xlClipboardFormatRTF Then
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If
Next
End If
End Sub

Regards,
Peter T

Not sure if any use but you can catch the paste menu click before it does
the paste

' normal module

Dim clsPasteEvnt As New Class1 ' suggest rename Class1

Sub SetPasteEvent()
'call from say Workbook_Activate and/or an Open event

Set clsPasteEvnt.ctrPaste = Application.CommandBars.FindControl(ID:=22)
End Sub

' in a class named Class1
' this won't work in XL97
Public WithEvents ctrPaste As Office.CommandBarButton

Private Sub ctrPaste_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)

If TypeName(Selection) = "Range" Then

Debug.Print Selection.Address

End If
End Sub

Maybe one of the Clipboard API gurus can catch the clipboard contents in
this event and/or with Ardus' suggestion (Ctrl-V ?).

Regards,
Peter T

 

[Tom Ogilvy]

Just some added info: Perhaps more is needed:

? application.CommandBars("Standard").Controls(12).Caption
&Paste
? application.CommandBars("Standard").Controls(12).ID
6002


set c = Application.CommandBars("Standard").FindControl(ID:=6002)
? c.Caption
&Paste

Search for 22 resulted in c being nothing.

 

[Peter T]

Search for 22 resulted in c being nothing.

I wonder if is XL version dependant, from the immediate window in my
XL2000 -

?Application.CommandBars.FindControl(ID:=22).Caption
&Paste

Regards,
Peter T

 

[Tom Ogilvy]

Yes it is. I am using xl2003 right now

But ID's have changed in the past as well. This particular button has
enhanced functionality - so it isn't just an ID change, but it did replace
the ID 22 button.