It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
Let’s assume you want Excel to re-calculate every time the selection changes.
You may be using a conditional format based on the active cell location.
So you could have an event macro like this
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calculate End Sub
The above macro clears the clipboard and stops you pasting after you have copied.
To handle the case where you have copied (or cut) and want to paste, we can check the status of the clipboard before we run the Calculate command.
We can use the CutCopyMode setting to do this.
If the setting is zero then that means the clipboard is empty and we can run our Calculate command.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.CutCopyMode = 0 Then Calculate End Sub
The only problem with this technique is when Ctrl + v or a right click is used to paste. They don’t clear the clipboard, so the macro won’t run because there will still be something on the clipboard.
You must press the Esc key to clear the clipboard and then the macro will start to run again.
By the way if you copy and then press Enter to paste that automatically clears the clipboard. Its old functionality that many people don’t know.