I need to restrict pasting in a spreadsheet to values only.  I am using Excel 2010.  Thank you all in advance for the help!

Try this:

http://www.mrexcel.com/archive/VBA/4522.html

However, you have asked this question before and never explained why you want to do this. If you would go into more detail on your questions, perhaps they would be easier to answer.

I need to restrict pasting in a spreadsheet to values only.  I am using Excel 2010.  Thank you all in advance for the help!

Format your cells as Protection > Hidden then protect sheet. Copied cells will paste as value automatically.

• Proposed as answer by CaffeineComa Wednesday, October 03, 2012 4:05 AM

Hi,

Heres an example and you can do it via the steps listed below:

1. Press Alt+F11 in Excel to open the Visual Basic Editor;
2. Double-click ThisWorkbook in the Project Explorer (press Ctrl+R if you cannot see it);
3. Copy and paste the following code into ThisWorkbook.

Dim rngPrevious As Range    ' The previous range.
Dim rngNext     As Range    ' The next range.
Dim blnSwitch   As Boolean  ' If it is the first selection change.

' ########################
' Event for Workbook Open.
' ########################
Private Sub Workbook_Open()
    blnSwitch = False
    
    ' Record the active cell.
    Set rngPrevious = ActiveCell
End Sub

' ######################
' Event for SheetChange.
' ######################
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ToggleEvents False
    If Application.CutCopyMode = xlCopy Then Target.Value = rngPrevious.Value
    If Application.CutCopyMode = xlCut Then Target.Value = rngPrevious.Value
    Target.ClearFormats
    ToggleEvents True
End Sub

' ###############################
' Event for SheetSelectionChange.
' ###############################
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not blnSwitch Then
        Set rngNext = Target
        blnSwitch = True
    Else
        Set rngPrevious = rngNext
        Set rngNext = Target
    End If
End Sub

' ####################################
' Toggle the application-level events.
' ####################################
Private Sub ToggleEvents(ByVal Status As Boolean)
    Application.EnableEvents = Status
End Sub

4. Save your Workbook and then restart it.

Best Regards

• Marked as answer by Cristin YanModerator Wednesday, September 26, 2012 8:56 AM

Hello Somkiat.   Thanks for your response.  This method still allows cell formatting to paste (cell borders specifically).  In my spreadsheet (an attendance form) the formatting of the cells varies in order to make the information easier for timekeepers to interpret.  In certain instances, it's easier for a user to copy and paste content across cells.  If the user pastes Values, there is no problem; however, if she uses the general paste (Ctrl+V) the formatted borders come along.  

Let know if you have another idea!

Thanks much!

Hello Cristin.  Thank you for your response.  Unfortunately, after installing this code, I still had full paste options in my workbook.  For instance, I could copy the content in cell A1 and paste it via any option in A2.  Let me know if I've done something wrong! :)

Again, many thanks for your time!

Hello,

All the cell format will be pasted with only the value after pasting (no matter you choose which paste option).

:)

Best Regards

• Edited by Cristin YanModerator Monday, September 17, 2012 2:51 AM
• Marked as answer by Estebanes Saturday, October 06, 2012 7:25 PM

This rocks!  Thanks Cristin!  Sorry for the slow response :O

Cheers!

Greetings,Not working for me.

There's an error popping out when I try to save or repone the workbook and try to write anything.

Error: Runtime error 1004: Cannot change part of a merged cell.

What causes this? Target.clearFormats @ sheetchange

If I have part of a pread sheet locked  to the user, and only want to lock the special paste on an area, this code is not for me?

This does not actually retain the original cell formatting.  It merely clears the cell formatting once the paste operation is completed.  If looking to retain original cell format, you will need to look elsewhere.

Hello 3 year old post - but I just ran into a similar situation and was frustrated as the original poster.

I'm not a VBA person at all, but I'm hoping to learn it in the future.

Without code - could you not utilize conditional formatting to reset the pasted formatting? For example, setting "Format only cells with: No Blanks (dropdown menu)" then format it to________?

Just trying to help.

Clifton

• Edited by CMcReynolds 20 hours 11 minutes ago