restrict spreadsheet to paste values only

jcellens · July 25th, 2006, 12:18 PM

is there a way to restrict a workbook to paste-values only?
i have a workbook set up as a form where customers input data and i don't want them to be able to paste formatting into the cells only values.

MarioP · July 26th, 2006, 02:28 AM

This is an example that solve your problem (example to copy value only from sheet S1 to sheet S2):

Sheets("S1").Select
Cells.Select
Selection.Copy
Sheets("S2").Select
Selection.PasteSpecial Paste:=xlPasteValues

Ciao

Mario

jcellens · August 10th, 2006, 03:06 PM

actually that doesn't help me. you just gave me code that performs a canned copy/paste function. what i need to do is restrict the user's functionality. i want to code the sheet so that it won't allow the user to paste formatting (i.e so that anytime the user does a paste, it automatically only pastes values). any suggestions?

jrogers · August 14th, 2006, 04:34 AM

Ok.. I think this will cover what you are looking for.. first to disable Ctrl-V the simplest way is to write a macro with Ctrl-V as the shortcut. so record a new macro and call it PasteValues and add v as the shortcut key, press ok, then stop recording. go to macro editor Alt-F11 and add following code

Code:

Sub PasteValues()

' Keyboard Shortcut: Ctrl+v

    On Error Resume Next

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

Sub Auto_Open()


    Application.CommandBars("Cell").Reset
        With Application.CommandBars("Cell")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click cell paste to use Pastevalues macro
            .Controls(4).Delete ' removes paste secial option
        End With

    Application.CommandBars("Row").Reset
        With Application.CommandBars("Row")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click row paste to use Pastevalues macro
            .Controls(4).Delete ' removes paste secial option
        End With

    Application.CommandBars("Column").Reset
        With Application.CommandBars("Column")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click column paste to use Pastevalues macro
            .Controls(4).Delete ' removes paste secial option
        End With

    Application.CommandBars("Worksheet Menu Bar").Reset
    With Application.CommandBars("Worksheet Menu Bar").Controls
        .Item(2).Controls(6).OnAction = "PasteValues"  ' changes edit/paste to use Pastevalues macro
        .Item(2).Controls(7).Enabled = False ' disable paste special
    End With

    Application.CommandBars("Standard").Reset
    With Application.CommandBars("Standard").Controls.Item(12)
        .Enabled = False
    End With
End Sub

any questions let me know

mdebatti · December 12th, 2008, 05:30 PM

I used this VBA code in one of my workbooks.

It works fine, but when the workbook containing this code is closed and data is pasted to any other spreadsheet using the right-click mouse paste shortcut, excel is looking for the file where this code is stored.

This does not happen with keyboard shorcuts ctrl-v / ctrl-c

How this prevent this from happening?

Any help would be greatly appreciated!

Simple Mind · April 12th, 2009, 01:10 AM

Hello folks,

I had the same issue with a patient information spreadsheet I created. I created a simple workaroud by protecting the sheet with editing permissions on the ranges I specified. Within the ranges where editing is permitted only the values can be changed, and therefore once the sheet is locked, only the values can be copy and pasted. It's worth noting that the "cut" function is disabled in a sheet that is protected as such, but that's just one extra step of deleting the original cell contents once the copy and paste is completed.

best of luck

bretti · June 30th, 2011, 12:43 AM

Hello,

I used the macro you provided above but now all of my spreadsheets don't have a paste special option and the paste option via mouse give an error (400).

Is there any way to reverse the changes this macro makes? I have deleted the macro and tried re-installing excel.

Thanks so much.

bretti · June 30th, 2011, 12:44 AM

did you find a solution?

gazpw27 · November 8th, 2011, 07:40 AM

I'd be interested to know the solution too, as I very stupidly applied the macros to one of my workbooks before reading to the end of the thread and now I cant use right click and paste and any workbook.

Thanks

mtranchi · November 8th, 2011, 07:20 PM

As far as Ctrl+v, I'd change the shortcut key so that it no longer over-rides the default behavior and as far as the rest of the code, I'd try

Code:

Application.CommandBars("Cell").Reset

only on each command bar item originally changed and see if that resets it. (Not gonna test it as I don't want to be stuck with the same problem.) If reinstalling Excel didn't solve the problem, I'm guessing that you'll have to deal with some Registry entries. Type "Regedit" at a command line to get into the registry editor, but be careful in the registry editor, you can wreak havoc if you don't know what you're doing. Set a system restore point before you make changes to the registry. The other thing to check is Users > YourUserName > AppData, not sure that's the correct path, but once you get to that folder, check around for an Excel folder, then take it from there.

As far as a solution to your original problem, I'd need some more info, but two things you can try is 1, put your macro into the Personal Macros workbook, which then will be available whenever Excel is open, or 2, in the workbook you have your macro in, use the Workbook_Close event to cancel it being closed unless all other workbooks are closed. Something like this:

Code:

Sub Workbook_Close(Cancel as Boolean)

If Workbooks.Count > 1 Then
  MsgBox "You must close all other workbooks before you can close this one."
  Cancel = True
End If

End Sub

Hope that helps