Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Disable copy paste function without VBA code

Copper Contributor

Hello, need some guidance in Excel. We have standard Excel format in office and customer need to update the data in the same format however they overwrite the drop down list by copy pasting the data either from same excel or from another excel sheet. How to protect or stop copy paste on drop down column but select from drop-down option enable and save the file. I tried using protect sheet function but then drop-down function cannot be selected and save. Also VBA coding is not allowed.

4 Replies

@dhirajashet24 

How to protect or stop copy paste on drop down column but select from drop-down option enable and save the file, without VBA?

 

The following steps may help.

  1. Select all cells with drop down list in the worksheet you need to protect. Click Find & Select > Data Validation under Home tab.
  2. Right click on them and select Format Cells in the right-clicking menu. In the Format Cells popup, go to Protection tab and uncheck Locked box. Click OK.
  3. Go to Review tab and click Protect Sheet button. In the Protect Sheet popup, check Protect worksheet and contents of locked cells option and enter a password if needed. Click OK.

This way, you can protect your worksheet from copying or pasting but still allow users to select from drop-down options.

Hope I could help you with these information.

 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE 

 

Ok here's my situation. I have an absurdly complex (by our standard, at any rate) budget that I designed for my department to use for federal grants. It involves formulas that are longer than the width of my screen and over a thousand lines of VBA. The intent is that anyone that understands how federal grants work can use it to enter in the appropriate information without having to be great at math or know (most of) the different exceptions. Somehow, someway, my boss (who is very intelligent but a bit more boomer than I am) kept breaking the budget. 

IT WAS DRIVING ME CRAZY. Formulas would randomly stop working. Formatting would be changed despite me having a listener that relocks the spreadsheet every time something changes. I spent MONTHS trying to figure out what she was doing, then a day or two ago I finally saw it in action. She was copy/pasting by right-clicking instead of using ctrl+v (boomer) and picking one of the clipboard icons (w/o formatting, I think?). It FOR SOME REASON allows you to bypass restrictions on editing locked cells if you are cut/pasting or copy/pasting in this manner.

This is a HUGE problem for me because there are <does some quick math> Literally 200+ formulas of references that she could accidentally break by copy/pasting in this way, and it is not always obvious to her that they are broken (tbf, it wouldn't be obvious to 99.999% of people b/c the errors induced can be so small relative to the amount of the grant). Do you know of any way to prevent copy/pasting? It is bypassing formula protection and giving me no end of fits. I'm literally to the point of - and I am not joking - replacing all formulas with VBA that she can't mess with. I'm hoping someone on the interwebs (you?) has a better idea b/c that would be really, really time-consuming and inefficient. It's mind-boggling to me that the built in "protect" bit doesn't work.

 

Again, you can't click something you aren't supposed to click on, but you can alter formatting and cause reference problems by (for example) copying from a cell that is merged into another merged cell of the same size which (for some reason?) is causing the cell receiving the new values to split into two cells again. 

I'm not sure if it is any help. But have you tried: Define a "Allow Edit Ranges" where users can add edit only those cells. Then protect the entire workbook/sheet. Where only tick "Select locked Cells" & "Select unlocked cells" option.
Or you can try this - add a listener for copy paste, and overwrite the pasted values.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pastedRange As Range
Dim cell As Range

On Error Resume Next
Set pastedRange = Intersect(Target, Me.UsedRange)
On Error GoTo 0

If Not pastedRange Is Nothing Then
Application.EnableEvents = False

For Each cell In pastedRange
' Convert formulas to values
If cell.HasFormula Then
cell.Value = cell.Value ' Replace formula with value
End If

' Clear cell formatting
cell.ClearFormats
Next cell

Application.EnableEvents = True

MsgBox "Pasted values have been converted to values and formatting has been cleared."
End If
End Sub

@s0mething 


TL;DR - The workbook is already locked. She is copying from editable cells to other editable cells so it's not helping. What's confusing is that she is able to sometimes change formatting this way despite the fact that it's locked and allow formatting is not enabled. Clearing out what she pasted won't work since it wouldn't fix the reference/unmerge/formatting issues. Idk how, but when she copy/pastes, sometimes it takes the reference w/ it. Ex: She types $1,000 in the "Lab supplies" field for year 1, copy/pastes or inadvertently drags it to year 2, and now there is a 0.1% chance year 2's budget total does not include the lab supplies field. I have had an incredibly difficult time replicating this error, but I have seen her do it twice so I'm 99% sure this is how she's doing it. She does it very quickly though (she's a fast worker) so I may be missing something. Possible results when she does this:

 

1) Changed reference. This template has formulas so long you have to stretch excel across two monitors to see them, a ton of conditional formatting, and over 1000 lines of VBA. She has no background in programming or spreadsheets and will not recognize that she's changed a reference. The entire point of locking the spreadsheet and disabling all options other than "allow unlocked cells" was so that she couldn't do this.

 

2) Unmerging cells at random. Again, the entire page is protected and "allow formatting" isn't enabled. I'm not sure how she's doing it.

 

3) Carry formatting from an old cell such as borders forward to the new cell. This isn't the end of the world, but it's another example of how she's going through the protection. Formatting is not enabled; even after this error, she cannot change formatting if she tries to.

 

One possibility that I've discarded is that I do have a listener that responds to certain cells. For example, one cell called numSKP allows the user to dynamically customize how many rows of SKPs will be shown vs hidden. In order to do this, the vba briefly unlocks the program then relocks it. I use the same listener module for ~10 cells, and it occurs to me as I type this that it unlocks the second it detects a cell change and locks again once it finishes processing. The issue is that lock/unlock process happens in like 0.001 seconds and when I put a pause in there (msgbox("If it's broken now, she broke it during the listener")), I was never able to trigger it during the listener. I don't think that's the cause, but tbf I was never able to trigger the error outside of the listener, either :grinning_face_with_sweat:. The issues she's happening are not related to the cells it's listening for, so I don't think this is it but I can't swear to it.