Permitting Paste Values Only

[markkramer]

Hi!

Is there a way to protect a worksheet that either allows paste values only but does not allow pastes? Or, better yet, is there way to protect a worksheet so that any pastes are always treated as paste values?

I have a workbook that uses Data Validation. Understandably, my users often paste data from another workbook into this one and that replaces the entire contents of the cell including the data validation. I'd like to find away that ensures that only the values are pasted without removing the data validation.

Thanks for your help in advance!

M

 

[Emjaye]

Hi Mark

I have tried several methods over the last few years, getting better all the time (I think) - here is my latest....

Option Explicit

Dim CB As CommandBar
Dim CBC As CommandBarControl

Private Sub SetPaste()
On Error Resume Next
    Application.EnableCancelKey = xlDisabled
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

Private Sub SetEnterReturn()
On Error Resume Next
    Application.EnableCancelKey = xlDisabled
    ActiveCell.Offset(1, 0).Select
End Sub

Private Sub DeSetCut()
On Error Resume Next
    Application.EnableCancelKey = xlDisabled
    Selection.Copy
End Sub

Private Sub WB_Activate() 'call or paste this code into workbook_activate event
On Error Resume Next
    With Application
    .EnableCancelKey = xlDisabled
    'set commandbars
    For Each CB In ThisWorkbook.Application.CommandBars
    CB.Visible = False ' generates error if "error resume next" omitted
    Next CB
    .CommandBars("worksheet menu bar").Enabled = True
    .CommandBars("formatting").Visible = True
    .CommandBars("standard").Visible = True
    'set cut, copy & paste
    For Each CBC In Application.CommandBars.FindControls(ID:=21)
    CBC.OnAction = "desetcut"
    Next CBC
    For Each CBC In Application.CommandBars.FindControls(ID:=22)
    CBC.OnAction = "setpaste"
    Next CBC
    For Each CBC In Application.CommandBars.FindControls(ID:=755)
    CBC.OnAction = "setpaste"
    Next CBC
    .CellDragAndDrop = False    'DRAG & DROP
    .OnKey "^v", "SetPaste"
    .OnKey "^x", "DeSetCut"
    .OnKey "{ENTER}", "SetEnterReturn"
    .OnKey "{RETURN}", "SetEnterReturn"
    .CommandBars("Standard").Controls("Paste").Enabled = False
    .CommandBars.DisableCustomize = True
    .ScreenUpdating = True
    End With
    'end set
End Sub

Private Sub WB_Deactivate() 'call or paste this code into workbook_deactivate event
On Error Resume Next
    With Application
    .EnableCancelKey = xlDisabled
    'reset cut, copy & paste
    For Each CBC In Application.CommandBars.FindControls(ID:=21)
    CBC.OnAction = ""
    Next CBC
    For Each CBC In Application.CommandBars.FindControls(ID:=22)
    CBC.OnAction = ""
    Next CBC
    For Each CBC In Application.CommandBars.FindControls(ID:=755)
    CBC.OnAction = ""
    Next CBC
    .CellDragAndDrop = True     'DRAG & DROP
    .OnKey "^v"
    .OnKey "^x"
    .OnKey "{ENTER}"
    .OnKey "{RETURN}"
    .CommandBars("Standard").Controls("Paste").Enabled = True
    .CommandBars.DisableCustomize = False
    End With
    'end set
End Sub

The WB_activate routine searches all commandbars and reassigns their actions to my chosen action, and for the keys too.
WB_deactivate resets them to their defaults.

(controls id's : 21 = cut, 19 = copy, 22 = paste & 755 = paste special)