VBA to enable copy/ paste as values OR formulas ONLY

[esbencito]

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??

[Gregor y]

a heavy handed approach would be to re-build the conditional formatting using an event such as the sheet's change event.

Private Const QO As String = """"
Private Const EditableRangeAdresses As String = "A1:B12,D1:E12"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RngToUpdate: Set RngToUpdate = Intersect(Target, Me.Range(EditableRangeAdresses))
    If RngToUpdate Is Nothing Then Exit Sub
    
    Application.EnableEvents = False: On Error GoTo FINALLY
    
    RngToUpdate.FormatConditions.Delete
    Set fc = RngToUpdate.FormatConditions.Add(xlExpression, , "=$O3=" & QO & "New" & QO)
    With fc.Interior
        .Pattern = xlSolid
        .ColorIndex = 4
    End With
    
FINALLY: Application.EnableEvents = True
    If Err Then
        On Error GoTo 0
        Resume
    End If
    On Error GoTo 0
End Sub

[esbencito]

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?

[Gregor y]

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

Application.OnKey

in ThisWorkbook

Private Sub Workbook_Open()
    Application.OnKey "+^v", "Pval"
'    Application.OnKey "^{INSERT}", "Pval"
End Sub

in a module

'paste by value with shift+ctrl+v
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
'    s.PasteSpecial Paste:=xlPasteFormats, SkipBlanks:=True, Transpose:=False
'    s.PasteSpecial Paste:=xlPasteComments, 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
    'Selection.EntireColumn.AutoFit
EOFn: Application.ScreenUpdating = True
    Exit Sub
PasteErr:
    'MsgBox Err.Description
    Resume PasteNorm
PasteNormErr:
    If Err = 1004 Then
        Beep
        On Error GoTo 0
        Resume EOFn
    End If
    On Error GoTo 0
    Resume
End Sub

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...

[Gregor y]

oh and since you're not doing it in your personal workbook you'd also want

in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "+^v"
'    Application.OnKey "^{INSERT}"
End Sub

[esbencito]

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!

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

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.

[Gregor y]

I haven't dug into the nitty-gritty of Application.OnUndo and Application.OnRepeat, but it doesn't look like the code you found would work for you since the actions it will Undo are actions performed by VBA not user actions; moreover the few poking around tests I did with OnUndo kinda worked, but it didn't preserve the stack so it'd fire the sub but you couldn't "undo" back into the user change history.

my simplistic attempts:

Private Sub Undo_Tst1()
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = 1
    
    Application.OnUndo "Do Nothing", "DoNothing"
    Application.OnRepeat "Do Nothing", "DoNothing"
End Sub

Public Sub DoNothing(): End Sub

I can put the question out there and see if anyone else has more experience with OnUndo, but since you "know" the formatting both conditional and otherwise that you want your sheet to have at the end of the day you're probably still better off using an event handler to rebuild/standardize it. Likely ThisWorkbook's Workbook_BeforeSave instead of the sheet's Worksheet_Change as I'd originally thought; since that should I think preserve all the undo stuff, not redirect any built in functionality, and be a little less heavy handed.

[MrShorty]

Responding to the question about undo: there is no way to preserve Excel's undo stack when VBA runs. It is possible to build your own undo handler from scratch to kind of take the place of Excel's undo stack/handler. See here: https://www.jkp-ads.com/Articles/UndoWithVBA00.asp