Is there a way to stop COPY/PASTE but still allow manual entries and use of drop down lists.

FrumpyJones

New Member
Joined
Feb 11, 2008
Messages
43
Hi Everyone,

SHORT VERSION: Is there a way to stop people from pasting lines from another worksheet into my worksheet, while still allowing them to input data and choose from dropdown lists and not corrupt my table/data validation/conditional formatting?

LONGER VERSION: I have enough excel knowledge to be an asset to my managers and a curse to myself. 😖

I built this spreadsheet that's capturing a lot of data and showed my mangers how with making the data a table, and using data validation and conditional formatting we can guide people on where to enter items and make them choose only certain items so that we can build a beautiful pivot table with slicing and whatnot so they have all the data they need when they go in front of the VP's. Works great. All Good.​
However, the managers now wanted ALL their supervisors to enter data into several different tracker worksheets they had me build (for 6 different departments...~50 people in a shared excel file-- kill me now).​
You can see where this headed... People are copy/pasting and destroying the tables, the data validation, and the conditional formatting.​

I've searched the forums here, and haven't seen my exact question. But in the closest-to-the-pin question I found (From over a year or so ago) I do see that:
  • this has always been an issue
  • the only possible solution is VBA code but it may have caveats
So, is there an Obi-wan here who can be this Leia's only hope? Or is it just me continually yelling at 50 supervisors to stop pasting data? 😭😤
P.S. VBA gives me night terrors, but I have a very very basic/101 understanding of it.

Thanks
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
from pasting lines from another worksheet into my worksheet
If you're sure that the text you copied is from Excel, not from any other app, then try this:
Say the range in question is C2:C10.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Akuini 18-Oct-2024
'This code prevents you from pasting into cells C2:C10.
'It only works if the text you copied is from Excel worksheet, not from any other app.

'Put the code in the sheet code module, this is how:
'Copy the code > open the sheet > Right-click the sheet tab >> View Code >> paste the code into the code window

    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
        If Application.CutCopyMode = 1 Then
            Application.CutCopyMode = 0
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
            MsgBox "Do not paste into cells C2:C10"
        End If
    End If
End Sub

If you need to apply this on multiple ranges, says "C2:C10" and "F5:F15", then replace this part:
VBA Code:
If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
with this:
VBA Code:
If Not Intersect(Target, Union(Range("C2:C10"), Range("F5:F15"))) Is Nothing Then

Example:
 
Upvote 0
I want to add something. To make the previous code (post #3) more robust you can add this code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
        If Application.CutCopyMode = 1 Then
            Application.CutCopyMode = 0
            MsgBox "Do not paste into cells C2:C10"
        End If
    End If
End Sub
just put it below the previous code.
 
Upvote 0
Have a look at this link:
This works really well. I do have a follow-up question, though:

In the link you gave, they gave another link to a VBA to force macros on (Here). Would this forcing macros on work for:
  1. People working in a shared file (I've seen 13 people at one time in the file)
  2. People working in the web-browser version
I ask because this code says it was only tested with 2003.

Thanks :)
 
Upvote 0
I want to add something. To make the previous code (post #3) more robust you can add this code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
        If Application.CutCopyMode = 1 Then
            Application.CutCopyMode = 0
            MsgBox "Do not paste into cells C2:C10"
        End If
    End If
End Sub
just put it below the previous code.
This works well, but you bring up a good point that I can't guarantee with 50 supervisors, they are all copying from an excel file into this file.
 
Upvote 0

Forum statistics

Threads
1,224,462
Messages
6,178,814
Members
452,880
Latest member
kares

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
Back
Top