VBA to enable copy/ paste as values OR formulas ONLY

Hi all,

I have a locked file in which clients put in the number of units they want to buy. Therefore, some of the cells must be unlocked for editing. The problem is, I use a lot of conditional formatting which gets messed up every time someone uses CTRL + C, CTRL + V as it copies not just the values but also the formats.

Is there a way to have a macro disable everything BUT pasting as values OR formulas? Maybe whenever they press CTRL + C, CTRL + V, a message appears asking if data should be posted as values or formulas? Or any similar feasible solution to this??

Thanks for sharing your sheet change event solution.

Unfortunately, as you have already mentioned, this is a heavy handed approach, as there isn't only conditional formatting, but also number formats and colours that are affected. Do you know of any way that the original CTRL + C, CTRL + V shortcut could be "hijacked" with code to default it to pasting it as values? Do you know if this is generally speaking feasible?

Originally Posted by Gregor y

Public Sub Pval()
Dim s As Range: Set s = Selection
If Application.CutCopyMode = False Then GoTo PasteNorm
Application.ScreenUpdating = False
On Error GoTo PasteErr
s.PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True, Transpose:=False
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
PasteNorm:
On Error GoTo PasteNormErr
s.PasteSpecial
On Error GoTo 0
s.WrapText = False
EOFn: Application.ScreenUpdating = True
Exit Sub
PasteErr:
Resume PasteNorm
PasteNormErr:
If Err = 1004 Then
Beep
On Error GoTo 0
Resume EOFn
End If
On Error GoTo 0
Resume
End Sub

Thanks Gregor! This is great, a big step in the right direction!

Originally Posted by Gregor y

Not recommended as it disables CTRL+Z, which is why I did mine for CTRL+SHIFT+V

Is there no way to incorporate an "undo" function with code? Clients wouldn't be pleased if they suddenly can't undo accidental copy/pasting.
I found the following article about such a function but my VBA skills aren't advanced enough to apply it to the above

https://www.jkp-ads.com/Articles/UndoWithVBA00.asp

Originally Posted by Gregor y

Note this solution also relies on knowing how the end user pastes their data, they could just as easily right-click and paste, SHIFT+INS paste...

It doesn't really matter if they could get around it by using right-click paste as etc.. I'm not trying to lock such features, just want to ensure that no one unknowingly messes up conditional formats as they aren't aware of the differences between pasting as values and CTRL+V. So they can still be able to paste formats/ formulas etc. but CTRL+C, CTRL+V should always default to "paste as values" as this is the shortcut 99% of people use.