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

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

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.

--
Regards,
Tom Ogilvy


"srinu1264" wrote:

>
> 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
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile: http://www.excelforum.com/member.php...o&userid=34155
> View this thread: http://www.excelforum.com/showthread...hreadid=544626
>
>

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 news:
[email protected]...
>
> 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
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile:
> http://www.excelforum.com/member.php...o&userid=34155
> View this thread: http://www.excelforum.com/showthread...hreadid=544626
>

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...

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


"srinu1264" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> srinu1264
> ------------------------------------------------------------------------
> srinu1264's Profile:
http://www.excelforum.com/member.php...o&userid=34155
> View this thread: http://www.excelforum.com/showthread...hreadid=544626
>

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


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> 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
>
>
> "srinu1264" <[email protected]> wrote
> in message news:[email protected]...
> >
> > 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
> >
> >
> > --
> > srinu1264
> > ------------------------------------------------------------------------
> > srinu1264's Profile:
> http://www.excelforum.com/member.php...o&userid=34155
> > View this thread:
http://www.excelforum.com/showthread...hreadid=544626
> >
>
>

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.
--
Regards,
Tom Ogilvy



"Ardus Petus" wrote:

> 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 news:
> [email protected]...
> >
> > 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
> >
> >
> > --
> > srinu1264
> > ------------------------------------------------------------------------
> > srinu1264's Profile:
> > http://www.excelforum.com/member.php...o&userid=34155
> > View this thread: http://www.excelforum.com/showthread...hreadid=544626
> >
>
>
>

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ardus Petus" wrote:
>
> > 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 news:
> > [email protected]...
> > >
> > > 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
> > >
> > >
> > > --
> > > srinu1264
> >
> ------------------------------------------------------------------------
> > > srinu1264's Profile:
> > > http://www.excelforum.com/member.php...o&userid=34155
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=544626
> > >
> >
> >
> >

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.

--
Regards,
Tom Ogilvy


"Peter T" wrote:

> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Ardus Petus" wrote:
> >
> > > 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 news:
> > > [email protected]...
> > > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > srinu1264
> > >
> > ------------------------------------------------------------------------
> > > > srinu1264's Profile:
> > > > http://www.excelforum.com/member.php...o&userid=34155
> > > > View this thread:
> http://www.excelforum.com/showthread...hreadid=544626
> > > >
> > >
> > >
> > >
>
>
>