3

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.

Community
  • 1
  • 1
mbehzad
  • 3,758
  • 3
  • 22
  • 29
  • 1
    Answer to your question is here: http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell – Radek Dec 11 '14 at 19:00
  • If you tell us what you're trying to achieve, we'll be able to suggest a good course of action. Right now this is a fishing expedition. – Jean-François Corbett Dec 11 '14 at 20:41
  • Exactly what are you trying to do? Capturing the Ctrl-C keystroke won't work if someone clicks the Copy button on the ribbon, or uses Ctrl-drag to copy the cell, or right-clicks and chooses Copy. – Tom Robinson Dec 11 '14 at 20:52
  • Would this be for something like, marking a date whenever the value has been copied? Or perhaps writing a note or comment somewhere? This is an interesting question. – peege Dec 12 '14 at 03:59
  • @Jean-FrançoisCorbett,Tom Robinson, 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. – mbehzad Dec 12 '14 at 08:41
  • Please put question clarifications in the question itself, not in comments. Your question can be edited by clicking the "edit" link. Did it for you this time. – Jean-François Corbett Dec 12 '14 at 09:03

3 Answers3

3

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.

enter image description here

Example in action when pressing Ctrl-Shift-C:

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
1

You can assign a macro to Cntrl+c with an OnKey statement.

See:

http://msdn.microsoft.com/en-us/library/office/ff197461(v=office.15).aspx

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Is there any way to handle the RIGHT CLICK copy method, or even a macro copying the value? Basically, the value was READ by a process in some official way. – peege Dec 12 '14 at 04:01
  • but would i loose the copy function? – mbehzad Dec 12 '14 at 08:54
  • 1
    @user2520818 You could have the macro perform **Selection.Copy** as the first order of business – Gary's Student Dec 12 '14 at 12:02
  • @Gary'sStudent Not necessarily the "first order of business". Depending on usage, `Selection.Copy` might be the *last* line in the keydown-proc, not the first line. Ie, any operations which cancel the copy need to go *before* `Selection.Copy`. Eg, `Application.Calculation = xlCalculationAutomatic` will cancel the copy operation, so that should go *before* `Selection.Copy`. – johny why Dec 01 '21 at 19:53
0

Here is an example of the OnKey statement suggestion given previously:

Application.OnKey "{UP}", "PressUp" Application.OnKey "{DOWN}", "PressDown"

"PressUp" and "PressDown" are the names of the subs that should be run. I would be hesitant about making a shortcut to run a macro that already has a pre-existing macro assigned to it. If you were to assign your macro to run when you pressed ctrl + c, it would overwrite the copy function that it already has.

Thomas Beck Sutton
  • 86
  • 5