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

[FrumpyJones]

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

 

[mumps]

Have a look at this link:

VBA Express : Excel - Disable Cut, Copy, Paste

Disable Cut, Copy, Paste It may be desirable to prevent users from cutting, copying, or pasting data into or from your workbook. This code disables all of those functions.

www.vbaexpress.com

 

[Akuini]

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.

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:

If Not Intersect(Target, Range("C2:C10")) Is Nothing Then

with this:

If Not Intersect(Target, Union(Range("C2:C10"), Range("F5:F15"))) Is Nothing Then

Example:

https://www.mediafire.com/file/b14b1d6mdsc84x5/Akuini_-_FrumpyJones_-_prevent_pasting.xlsm/file

 

[Akuini]

I want to add something. To make the previous code (post #3) more robust you can add this 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.

 

[FrumpyJones]

Have a look at this link:

VBA Express : Excel - Disable Cut, Copy, Paste

Disable Cut, Copy, Paste It may be desirable to prevent users from cutting, copying, or pasting data into or from your workbook. This code disables all of those functions.

www.vbaexpress.com

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

 

[FrumpyJones]

I want to add something. To make the previous code (post #3) more robust you can add this 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.