VBA event trigger on copy?

Question

Is there a method in VBA, where when in excel I copy a cell's value it triggers my function?

Or any work-arounds for that?

Or can I read key-downs and listen to Ctrl+C ?

Or can VBA read the content of copied text?

The reason is: I copy some entries from an Excel sheet to another program, and i would like to add automatically for example a gray font color to all entries which are already copied.

Answer 1

Assign a shortcut key to your macro.

For example, if your macro is this:

Sub CopyAndMarkAsCopied()
    Dim r As Range
    Set r = Selection
    With r
        .Copy
        .Font.Color = RGB(100, 100, 100) 'dark grey font
        .Interior.Color = RGB(200, 200, 200) 'light gray background
        'whatever else
    End With
End Sub

In Excel 2010, view macros in Developer > Macros (or keyboard shortcut Alt-F8). In earlier versions of Excel, the menus are a bit different, but the keyboard shortcut works the same.

Select your macro in the list, and click Options.... In this dialog, you can assign a shortcut key to your macro. It can either be:

• Ctrl-some letter, in which case you have to type a lowercase letter; or
• Shift-Ctrl-some letter; type an uppercase letter.

In this example I chose Ctrl-Shift-C (note the uppercase C in the screenshot below).

I probably wouldn't mask default keyboard shortcuts that I normally use, like Ctrl-C, but you're free to do so if that's your thing.

Example in action when pressing Ctrl-Shift-C: