Paste without format within workbook and from other applications

Question

I've read various posts about using VBA to prevent format changes to spreadsheets caused by the user pasting data.

I didn't like the approaches binding CTRL+V to a macro because the user may not use the shortcut, and some examples had the effect of pasting into any cell that was subsequently clicked on.

I've had most success with the code from https://www.mrexcel.com/board/threads/vba-for-pastespecial-values-only.355553/ which uses the undo function. This works (although it's a little slow) for copy and paste within the workbook, and with single or multiple cells, but not from other instances of Excel or other programs.

I found Excel vba paste special method fails whenever i try to paste which works with pastes into a single cell.

Is there a way to combine the two and achieve the aim of preventing paste from any source changing cell(s) formatting?

My approach is to have code that resets/updates formatting, validations, etc that is triggered by cell changes of any kind. Sometimes, it is fastest to have some hidden template ranges and you simply copy and paste special formats into the range the user had just pasted into. — Super Symmetry, Jul 06 '20 at 15:47
Interesting, entirely different approach. I like it. Do you have some example code? Do you restrict the macro to running only when certain cells are changed? I could make the hidden template cells locked down, copy their formats to the pasted cells then change the style of the pasted area to an unlocked style again. — artyb, Jul 06 '20 at 17:42
Any examples from anyone of this approach would be much appreciated. — artyb, Jul 09 '20 at 11:04
You will inevitably have to change any code and adapt it to fit your application. A good place to start is write down (on a piece of paper) a list of formats you want to preserve for all the different ranges -> Start the macro recorder and apply these formats one by one. You then play around with the generated code to make it a little more dynamic, and remove all those `Select` statements. Once you are happy with your code you can run it in a `Worksheet_Change` event handler. If you have problems along he way, come back with code you generated/adapted and poeple will try to help — Super Symmetry, Jul 09 '20 at 15:34

score 0 · Answer 1 · answered Jul 10 '20 at 17:12

I think I've written something useful that seems to work well for me.

I didn't implement your suggestion as it seemed fairly complex, and I found another forum post with a slightly different form of the 'undo' based scripts that worked well for copy and paste of text or single cells, or ranges from within an instance of excel or from external programs. I've combined that with the more common version I'd found which deals with 'Auto Fill' events too, and I've added some other little enhancements.

I've acknolwedged the other forum posts which I used too. Here it is in case it is of use to other people. I'm not an expert and I expect the code could be improved, but it does what I need.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'Prevents user changing cell formatting by undoing the paste and pasting the value only without formatting
    'Macro restricted to run on paste events to prevent the pasting of formats into the spreasheet, but not run on delete or type events
    'Works with ranges and individual cells, both within the workbook Excel instance and from other instances or external programs
    'A combination of:
    'https://www.mrexcel.com/board/threads/force-paste-special-values.230718/
    'https://stackoverflow.com/questions/45135039/excel-vba-code-to-force-values-only-paste-causes-strange-behavior-when-pasting-o
    'And:
    'https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-you-lock-formats-but-allow-data-entry/afccc43e-e191-417f-826c-d10a464a1b9a?page=4
    'A disadvantage of many macros, including this one, is that the undo history is cleared
    'Macro can be disabled for spreadsheet developement by changing the Devel value used in the first IF statement of the macro

    Dim Devel As Boolean
    Devel = "False" 'Remember to set back to False after spreadsheet development work
    If Devel = "False" Then
        'In normal user mode, prevent pastes etc from changing formatting
        Dim SavedVal As Variant
        Dim UndoString As String
        Dim srce As Range
        
        On Error GoTo ErrHan
        
        'Detect 'Paste' and 'Auto Fill' events in the undo list and only continue macro if these are found
        UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1) 'This always gives an error on spreadsheet open as the undo list is empty, but the error handling deals with it
        If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
            Exit Sub
        End If
    
        'Save the pasted value for later. This form and the use of type Variant is important in the flexibility of this macro
        SavedVal = Target.Value
    
        'Switch off events to prevent infinite loop
        Application.EnableEvents = False
        
        'Switch off screen updates
        Application.ScreenUpdating = False
    
        'Undo the user's paste
        Application.Undo
        
        'Handle 'Auto Fill' events differently. These can use the Target.PasteSpecial approach
        If UndoString = "Auto Fill" Then
            Set srce = Selection
            srce.Copy
            Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.SendKeys "{ESC}"
            Union(Target, srce).Select
        Else
            'Set target value. This form works with all selected data which the Target.PasteSpecial approaches don't
            Target.Value = SavedVal
        End If
    Else
        'In Devel mode so the developer can unlock the spreadsheet and change formats when pasting
    End If

ErrExit:
    'Remember to re-enable events
    Application.EnableEvents = True
    
    'Re-enable screen updates
    Application.ScreenUpdating = True
    Exit Sub

ErrHan:
    Resume ErrExit
End Sub

Paste without format within workbook and from other applications

1 Answers1