Forum Discussion
Disable copy paste function without VBA code
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.
