Disable only "paste" in excel (1 Viewer)

tihmir

Registered User.
Local time
Yesterday, 20:53
Joined
May 1, 2018
Messages
216
Hello, how to disable only "paste" in excel?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:53
Joined
Oct 29, 2018
Messages
18,962
Hi. That looks like a very broad question. Can you narrow it down a bit?
 

Isaac

Lifelong Learner
Local time
Yesterday, 20:53
Joined
Mar 14, 2017
Messages
6,454
This will seem very 'roundabout' idea, but:

Is the content people are pasting (which you want to prevent), always, or usually, more than one cell at a time?

IF "yes", then:

It is also true that almost always, any time an excel worksheet is 'changed', if > one cell at a time is involved, it is a Paste operation. (Usually, with the exception of auto-fill formulas, which they're probably not doing if they're your End Users, I assume).

Therefore:

You can write a few lines of code in the Worksheet_Change event as follows:

  1. Ensure your Excel workbook is saved as .xlsb first
  2. At the bottom of Excel, right-click on the name of the worksheet (tab), and choose View Code.
  3. Near the top, change the dropdown on the left, from "General", to "Worksheet"
  4. Now change the dropdown on the right to "Change"
  5. Use the code below to constitute the entire Sub.
Now people will be unable to Paste anything more than one cell at a time.
That may take care of 100% or 95% of your problem - Or, if you are worried about people pasting one single cell at a time, it won't do anything, but hope you had fun reading this post anyway :D

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler

Dim strAddress As String
strAddress = Target.Address
If InStr(1, strAddress, ":") > 0 Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    MsgBox "Sorry, Paste has been prevented.  Action has been un-done", vbCritical, "  "
End If

Exit Sub
errhandler:
Application.EnableEvents = True
MsgBox "An error has occurred in the paste prevention worksheet change event as follows: " & Err.Description, vbCritical, "  "
End Sub
 

isladogs

CID VIP
Local time
Today, 04:53
Joined
Jan 14, 2017
Messages
16,216
I've never done this in Excel but in Access I block copy & paste when necessary by
1. hiding the ribbon
2. disabling the right click context menu
3. blocking the Ctrl+C & Ctrl+V shortcut keys
See my video on the subject:
You should be able to do exactly the same in Excel
 

Users who are viewing this thread

Top Bottom