Stopping the Clipboard from being cleared in Excel VBA

How to check if the clipboard is empty

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.