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

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" a écrit
dans le message de news:
...

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=544626

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" wrote
in message ...

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
...
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" wrote
in message ...

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).C aption
&Paste
? application.CommandBars("Standard").Controls(12).I D
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" a écrit
dans le message de news:
...

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).Capti on
&Paste

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Just some added info: Perhaps more is needed:

? application.CommandBars("Standard").Controls(12).C aption
&Paste
? application.CommandBars("Standard").Controls(12).I D
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" a
écrit
dans le message de news:
...

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).Capti on
&Paste

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Just some added info: Perhaps more is needed:

? application.CommandBars("Standard").Controls(12).C aption
&Paste
? application.CommandBars("Standard").Controls(12).I D
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" a
écrit
dans le message de news:
...

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