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.

 

[BrianB]

Perhaps if you supply them with a button to PasteSpecial Values they with use this instead of the 4 mouse clicks required otherwise.

or ... Could run it with the BeforeDoubleClick event.

 

[blk]

I could add a button, but the worksheet already has quite a few and I don't want to make it more complicated than it has to be. If I could do it with code, that would be better I think. If not, a button's a good idea and I'll probably use that.

Cheers!!

 

[TommyGun]

This will stop them from being able to Paste a copy, and does not require another button. Add this to your sheet's code...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)

<SPAN style="color:#00007F">If</SPAN> Application.CutCopyMode = xlCopy <SPAN style="color:#00007F">Then</SPAN>

    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

    Application.Undo
    Target.PasteSpecial Paste:=xlPasteValues

    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[blk]

Thanks for that. Works like a dream!!

 

[Jaafar Tribak]

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:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()
    Customise_CutCopyPaste
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Deactivate()
    Restore_CutCopyPaste
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Customise_CutCopyPaste()
<SPAN style="color:#00007F">With</SPAN> Application
    .OnKey "^x", "CutSource"
    .OnKey "^v", "PasteTarget"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">With</SPAN> CommandBars("Edit")
    .Controls("Paste").OnAction = "PasteTarget"
    .Controls("Paste Special...").OnAction = "PasteTarget"
    .Controls("Cut").OnAction = "CutSource"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    Customise_CutCopyPaste
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Restore_CutCopyPaste()
    <SPAN style="color:#00007F">With</SPAN> Application
        .OnKey "^x"
        .OnKey "^v"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> CommandBars("Edit")
        .Controls("Paste").OnAction = ""
        .Controls("Paste Special...").OnAction = ""
        .Controls("Cut").OnAction = ""
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


2nd Part Of Code :


<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> CutMode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#00007F">Public</SPAN> SourceRange <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CutSource()
    CutMode = <SPAN style="color:#00007F">True</SPAN>
    ActiveCell.Copy
    <SPAN style="color:#00007F">Set</SPAN> SourceRange = ActiveCell
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> PasteTarget()
    <SPAN style="color:#00007F">If</SPAN> Application.CutCopyMode = xlCopy And CutMode = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        ActiveCell.PasteSpecial xlPasteValues
        SourceRange.Clear
        CutMode = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        ActiveCell.PasteSpecial xlPasteValues
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



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


Regards.

 

[Iridium]

http://www.mrexcel.com/board2/viewtopic.php?p=206642#206642
http://www.mrexcel.com/board2/viewtopic.php?p=239190#239190

HTH

 

[Jaafar Tribak]

Thanks Iridium.

Very nice codes in particular the one from Ivan F Moala!

However it is not exactly what we are trying to achieve here.

Regards.

 

[cambralenta]

This will stop them from being able to Paste a copy, and does not require another button. Add this to your sheet's code...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Application.CutCopyMode = xlCopy Then

****Application.EnableEvents = False

****Application.Undo
****Target.PasteSpecial Paste:=xlPasteValues

****Application.EnableEvents = True

End If

End Sub

I´m getting this error:

Run-time error '1004':
Method 'Undo' of object '_Application' failed

Any help please! Thanks

 

[cambralenta]

Ok, in same woksheet I have this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
If Not Intersect(Range("D9:AX38"), Target) Is Nothing And Target.Count = 1 Then
   Calculate
End If
End Sub