VBA for PasteSpecial Values ONLY???

[Mister H]

Hi All:

I was wondering if there is some VBA code that I can put in my spreadsheet that would only allow Paste Special Values?

I want to make sure that the user can not simply cut and paste. If possible it would be nice if the user doesn't know what is going on all they need to know is that they can copy and paste.

Is there a seamless way to accomplish this?

If you need further info just let me know.
THANKS,
Mark

 

[Mister H]

THANKS Mike, THANKS Mala SORRY for not responding sooner but I was away from my computer until now...

Your assistance is VERY much APPRECIATED.

Have an AWESOME day,
Mark

 

[jamilm]

THANKS Mike, THANKS Mala SORRY for not responding sooner but I was away from my computer until now...

Your assistance is VERY much APPRECIATED.

Have an AWESOME day,
Mark

can post the final code which worked for you?

 

[Mosdeb]

Exactly my thought when I responded to:
Need help TWEAKING this code...

I tried the code given by mike and it works indeed but it desactivates the undo function... please tell me if there is a way of changing the code so that the undo function remains active.
KR,
Mosdeb

 

[sonchip]

This is amazing and works great. I did however, remove the if string that contained the "meeting minutes" only because quite frankly, I don't mind upsetting them. We use excel for many things... and when we are doing our population statistics, it needs to be correct. But this is exactly what I was looking for!

You guys/gals are great! Thanks.

 

[happydonut]

Hi all,

can anyone tell me where to put this code for it to work?
I've tried creating regular Module, but didnt work. Also tried class module, didnt work. Thanks

THANKS again Mike...

Someone else pointed me to wards this code (I had a similar Post to the one you answered).

This code changes the Copy Paste to Copy PasteSpecial Values. I played with it a bit and it seems to work pretty good and it is seamless. I guess the only problem arises if someone actually wants to cut and paste all formats etc. Anyway, I thought I would share this with you and anyone that might happen to come across this post.

Does anyone know if this code can be altered to apply ONLY to a sheet titled (Meeting Minutes)?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
    Dim UndoString As String
    Dim srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
   
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
       
        Exit Sub
       
    End If
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
           
           
    If UndoString = "Auto Fill" Then
       
        Set srce = Selection
       
        srce.Copy
       
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                   
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
       
    Else
   
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                   
    End If
   
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

THANKS,
Mark

 

[MARK858]

Code needs to go in the ThisWorkbook module.
Find your file name in the Projects Window, look below it to find ThisWorkbook, right click ThisWorkbook and click View Code.