Should I turn .CutCopyMode back on before exiting my sub procedure?

by

The short answer is that Application.CutCopyMode = True either does nothing at all or it does the opposite of what you think it does. If the current state of the .CutCopyMode is False then setting it to True does not turn on the ‘dancing border’ and if the current state is either xlCopy or xlCut (XlCutCopyMode Enumeration constants), setting .CutCopyMode to True effectively turns it off. Additionally, you cannot set the .CutCopyMode to xlCopy or xlCut.

The long story starts with understanding just what purpose the Application.CutCopyMode property performs and how the official documentation wrong implies that you can turn it back ‘on’.

When you manually cut (Ctrl+X) or copy (Ctrl+C) one or more cells on the worksheet, the cells will acquire a ‘dancing border’¹ that indicates the source of the operation. At this point, .CutCopyMode is non-zero (either xlCopy or xlCut) and a relationship between subsequent paste operations and both the Office clipboard and Windows clipboard exists in that they are retaining the content of the source.

If you elected to cut (aka move) the cells, immediately after pasting (Ctrl+V) the cells to a new location .CutCopyMode becomes false and you lose the ‘dancing border’ around the source. This is because there is no content left in the source cells. The content remains accessible from the Office clipboard but is removed from the Windows clipboard.

If you elected to copy the cells, you can paste the cells to an additional location and the ‘dancing border’ remains. The .CutCopyMode property remains non-zero (e.g. xlCopy). You can move to another location and paste the same content; .CutCopyMode remains non-zero and the ‘dancing border’ around the original content persists as does the relationship to both the Office clipboard and the Windows clipboard.

If you ran VBA code that included Application.CutCopyMode = False at this point, the dancing border would disappear and the connection between any paste operation and the Office clipboard would be eliminated. This is a good state to be in when initiating a VBA sub procedure so that any potential copy/paste operation within the code could not potentially conflict with the .CutCopyMode state. However, this should only be necessary in special circumstances (see next paragraph).

Certain Excel operations are sufficient to break this Office clipboard connection and force .CutCopyMode to False. One of these is manually initiating a macro sub procedure so there is limited benefit to including Application.CutCopyMode = False at the beginning of your code. However, it may be prudent to run within your code if your code has initiated a Range.Copy operation and you have completed any Worksheet.Paste method or Range.PasteSpecial method operations with the contents of the copy.

Examining and reporting the current state of the .CutCopyMode can be done with some worksheet event macro code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Application.CutCopyMode
        Case True
            Debug.Print "CutCopyMode is ON"
        Case xlCopy
            Debug.Print "CutCopyMode is in Copy mode"
        Case xlCut
            Debug.Print "CutCopyMode is in Cut mode"
        Case False
            Debug.Print "CutCopyMode is OFF"
        Case Else
            Debug.Print "???"
    End Select
End Sub

The results reported to the VBE’s Immediate window will be either Copy mode, Cut mode or OFF. Application.CutCopyMode will never directly report its state as True².

While you can effect a change in the operational environment with Application.CutCopyMode = False, I have never been able to turn .CutCopyMode On by setting the property to True. No error is thrown and the official documentation specifically states that setting the property to True “Starts Cut or Copy mode and shows the moving border.” but I have found that the only way to get back the ‘marching ants’ is to initiate another copy operation.

So for all intents and purposes, coding Application.CutCopyMode = True does no harm. However, coding Application.CutCopyMode = False can perform some good by relinquishing clipboard storage.

If anyone can turn the marching ants back on by manipulating the Application.CutCopyMode property, I would dearly love to see an example.

¹ The ‘dancing border’ is also known colloquially as ‘marching ants’.
² While a boolean value of True or False is a distinct type, for all intents and purposes False equals zero and anything that is not False is True. If resolving boolean ► number, a VBA False is 0 and True is always equal to (-1) but if resolving the reverse number ► boolean any non-zero number is considered True and a zero is considered False.

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Getting ScriptControl to work with Excel 2010 x64
  3. Pop up the Excel Statusbar?
  4. Get list of Excel files in a folder using VBA [duplicate]
  5. When one should use Set [e.g for SpecialCells return value]?
  6. Determine if cell contains data validation
  7. Sheet protection: UserInterFaceOnly gone
  8. VBScript – How to make program wait until process has finished?
  9. How to highlight a cell using the hex color value within the cell?
  10. Removing special characters VBA Excel
  11. Open an Excel file from SharePoint site
  12. VBA recognizing workbook by partial name
  13. Excel VBA wildcard search
  14. How do I loop an excel 2010 table by using his name & column reference?
  15. Excel VBA Project has generated multiple Workbook objects
  16. Copy an entire worksheet to a new worksheet in Excel 2010
  17. delete Autofilter Field resullt using the header Name
  18. So, I have 6 “master” files to then divide into 40 separate files
  19. Application.Match gives type mismatch
  20. Count and Highlight keywords within phrases
  21. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  22. Parse HTML content in VBA
  23. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  24. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  25. Excel VBA: AutoFill Multiple Cells with Formulas
  26. VBA.Find and adding a value
  27. How to continue the code on the next line in VBA
  28. Excel VBA Userform – Execute Sub when something changes
  29. Finding first blank row, then writing to it
  30. Query my excel worksheet with VBA

Leave a Comment