Force Paste Special Values

[blk]

I have a template that other users will need to paste data in to certain cells. The user is supposed to paste the values only.

If the user just uses Paste, instead of Paste Special Values, I want excel to force a paste special values, as I want the formatting to remain the same. This can apply for the entire sheet, not just a specific range of cells.

I'm not sure if this can be done, but I thought I'd give it a go.

 

[cambralenta]

I fix it! Case closed...

 

[DomAtelier]

Jaafar, this is extremely helpful thank you for sharing.

Unfortunately for me it did not work: I pasted this into the standard and worksheet modules precisely as per your instructions. Any further guidance you can provide would be most appreciated

Hi TommyGun and Blk,

The code posted is great however it only works for copying but NOT moving data.

So if the User makes a Cut /Paste, the data is pasted together with all the Formatting !

To achieve the same result with Cut & Paste, place the first code below in the WorkSheet Module and the Second one in a Standard Module :


1st Part Of Code:


Private Sub Worksheet_Activate()
Customise_CutCopyPaste
End Sub

Private Sub Worksheet_Deactivate()
Restore_CutCopyPaste
End Sub

Public Sub Customise_CutCopyPaste()
With Application
.OnKey "^x", "CutSource"
.OnKey "^v", "PasteTarget"
End With

With CommandBars("Edit")
.Controls("Paste").OnAction = "PasteTarget"
.Controls("Paste Special...").OnAction = "PasteTarget"
.Controls("Cut").OnAction = "CutSource"
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Customise_CutCopyPaste
End Sub

Public Sub Restore_CutCopyPaste()
With Application
.OnKey "^x"
.OnKey "^v"
End With

With CommandBars("Edit")
.Controls("Paste").OnAction = ""
.Controls("Paste Special...").OnAction = ""
.Controls("Cut").OnAction = ""
End With
End Sub



2nd Part Of Code :


Public CutMode As Boolean
Public SourceRange As Range

Public Sub CutSource()
CutMode = True
ActiveCell.Copy
Set SourceRange = ActiveCell
End Sub

Public Sub PasteTarget()
If Application.CutCopyMode = xlCopy And CutMode = True Then
ActiveCell.PasteSpecial xlPasteValues
SourceRange.Clear
CutMode = False
Else
ActiveCell.PasteSpecial xlPasteValues
End If
End Sub




I must admit the codes seem a bit sloppy to me. I hope someone has a simpler solution !


Regards.

 

[Jaafar Tribak]

Jaafar, this is extremely helpful thank you for sharing.

Unfortunately for me it did not work: I pasted this into the standard and worksheet modules precisely as per your instructions. Any further guidance you can provide would be most appreciated

That code works only in Office 2003 and earlier editions.

For excel 2007 or later , use RibbonX http://www.rondebruin.nl/tips.htm