Forum Discussion
Disable copy paste function without VBA code
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.
- Select all cells with drop down list in the worksheet you need to protect. Click Find & Select > Data Validation under Home tab.
- 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.
- 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.
I know I don't know anything (Socrates)
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.
To address the problem of users inadvertently breaking formulas and formatting in a protected Excel workbook without using VBA, there are a few approaches you can take. Although completely disabling copy-paste isn't possible without VBA, you can mitigate the issue by using data validation, worksheet protection settings, and designing the sheet in a way that reduces the likelihood of errors.
Approach 1: Using Data Validation and Worksheet Protection
- Unlock Cells for Data Entry:
- Select all the cells where you want users to enter data.
- Right-click and select Format Cells.
- Go to the Protection tab and uncheck Locked.
- Click OK.
- Apply Data Validation:
- Select the cells with drop-down lists or data entry.
- Go to Data tab -> Data Validation.
- Set up your data validation criteria.
- Protect the Worksheet:
- Go to the Review tab.
- Click on Protect Sheet.
- Ensure Protect worksheet and contents of locked cells is checked.
- Ensure Select unlocked cells is checked.
- Enter a password if required and click OK.
Approach 2: Use Conditional Formatting to Highlight Issues
- Highlight Cells with Potential Errors:
- Use conditional formatting to highlight cells where formulas or formatting have been altered.
- Go to Home tab -> Conditional Formatting.
- Create a rule to highlight cells that deviate from expected formats or values.
Approach 3: Design the Sheet to Minimize Errors
- Use Separate Sheets for Data Entry and Calculations:
- Separate the data entry areas from the calculation areas.
- Use references to link the data entry sheet to the calculation sheet.
- Protect the calculation sheet entirely, allowing no changes.
- Provide Clear Instructions:
- Add comments or notes to guide users on how to correctly enter data without breaking the structure.
- Use color coding or shading to differentiate between editable cells and protected cells.
- Restrict Formatting Changes:
- Ensure that in the Protect Sheet dialog, options like Format cells, Format columns, and Format rows are unchecked to prevent users from making formatting changes.
Approach 4: Monitoring and Error Checking
- Use Built-in Excel Features for Error Checking:
- Enable Excel’s built-in error checking features to catch common errors.
- Go to File -> Options -> Formulas.
- Ensure Enable background error checking is checked.
- Use Helper Columns for Validation:
- Create hidden helper columns that validate data entry and highlight discrepancies.
- Use functions like IF, ISERROR, or COUNTIF to create checks.
Example Implementation
Unlock and Protect Cells:
// Select cells A1:A10 (example for data entry)
- Right-click -> Format Cells -> Protection -> Uncheck Locked
- Data -> Data Validation -> Set criteria
- Review -> Protect Sheet -> Check "Protect worksheet and contents of locked cells" and "Select unlocked cells"
Conditional Formatting for Errors:
// Highlight cells with invalid data
- Select range
- Home -> Conditional Formatting -> New Rule
- Use a formula to determine which cells to format: =ISERROR(A1) (example)
- Set desired formatting
Separate Data Entry and Calculation Sheets:
// Example of linking sheets
- Data Entry in Sheet1
- Calculations in Sheet2
- In Sheet2, reference Sheet1: =Sheet1!A1
- Protect Sheet2 completely
By following these approaches, you can significantly reduce the risk of users breaking your formulas and formatting, even without disabling copy-paste functionality directly. These methods provide a robust way to manage user input and maintain the integrity of your workbook. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
NikolinoDE After nearly a year of trying to replicate this error, it finally happened to me instead of to a user. What happens is if you click the boundary between two cells and drag it just a bit, it will break the formatting and split the merged cell back into two cells (and empty the formula and break references to the cell in the process). This happens despite it being a locked spreadsheet. I thought she was unconsciously copy/pasting, but actually it was just sloppy clicking apparently. I have not discovered a way to prevent this, so she still has the error on occasion. When it happens, I have to go into her instance of the spreadsheet and manually re-merge the cell and re-type all references to the cell as well which is a pain. Idk how to prevent it; I had assumed that locking the spreadsheet so that formatting wasn't allowed would prevent things like this, but I can replicate the error now and it does not.
- 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
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 😅. 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.
Marthurvin I have experienced something similar. I have a file that is locked except for a few cells that an be edited and I kept getting #REF errors with some of my formulas, despite all of those being locked. I believe people are cutting and pasting values into the box, sometimes from adjacent cells and this breaks the formula every time. I need to disable the ability to do a cut/paste.
