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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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)
 

Forum statistics

Threads
1,141,152
Messages
5,704,594
Members
421,358
Latest member
Redstar13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top