Permitting Paste Values Only

markkramer

Board Regular
Joined
May 8, 2002
Messages
162
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Emjaye

Board Regular
Joined
Oct 3, 2003
Messages
89
Hi Mark

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

Code:
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,536
Members
410,547
Latest member
htran4
Top