Alternative methods to launch a VBA macro in Excel, 3 of 3

Hello,

This is the third of three follow-up questions from the following thread:

          Shortcut for selecting the contents of a cell in Excel

The question there concerned asking for a way to select the contents inside of a cell rather than the cell itself and, if possible, do it more quickly and in fewer steps than are normally required. Copying the contents currently involves:

          • double-clicking the cell, selecting its contents, and then copying, or
          • clicking the cell, pressing F2, selecting its contents, and then copying, or
          • clicking the cell, selecting the contents in the Formula bar, and then copying.

One of the Experts in that thread provided the following VBA code:

>>rorya:
This will copy the activecell's formula (or value if not formula) to the clipboard:
Public Sub CopyCellContents()
   Dim objData           As Object
   On Error Resume Next
   Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
   With objData
      .SetText ActiveCell.Formula
      .PutInClipboard
   End With
End Sub

Open in new window

...followed by this as options to trigger the code:

>>rorya:
You...can:
1. Add it to the QAT or Ribbon so an Alt+key shortcut would work; or
2. Add it as a button on the right-click cell shortcut menus; or
3. Create an addin that monitored say a cell double-click with the Ctrl key held down.


In this thread, I am asking for an explanation and/or an example of how to run the code using option #3:

          Create an addin that monitored say a cell double-click with the Ctrl key held down.

(Note:  for this third option, my preference is to use Ctrl+single-click rather than the double-click.)

Thanks
Steve_BradyAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Here is a sample add-in. Note that the Ctrl key is problematic, since it is used for selecting multiple cells, so doesn't work that well with double-click (and not at all with single-click) - for this demonstration, you need to press the right control key and release it, then double-click the cell to copy the contents.
CopyCellContents.xlam
0
 
Steve_BradyAuthor Commented:
>>rorya:
Note that the Ctrl key is problematic, since it is used for selecting multiple cells...


Thanks Rory.

I realized that point about Ctrl+single-click only a short while after posting this thread.  

(FYI, because of paralysis in my hands, double-clicking is a challenge.  I do have a small program however which executes a left click whenever I stop moving the mouse.  It's been a phenomenal asset for me because it allows me to rest my left hand on the trackball and move to wherever I want and then simply stop moving to get the click.)

A better option (for me) would be to utilize the AppsKey which I never use for anything anyway. On my keyboard, the bottom row going from left to right is:

 | Ctrl | Win | Alt | Spacebar | Alt | Win | AppsKey | Ctrl |

Therefore, the AppsKey is also just in front of my right hand so if I could simply press and hold the AppsKey then click while still holding it, that would be perfect.

Is that doable?


Thanks again
0
 
Rory ArchibaldCommented:
I don't know offhand - certainly not on the Mac, which is the only computer within reach at the moment. ;)

I will experiment tomorrow.
0
 
Steve_BradyAuthor Commented:
>>rorya:
I will experiment tomorrow.


No need Rory.  I got this working using some AHK code. It turns out that I already had my F8 key remapped to function as Ctrl+C.  Now it is set up so that if I double-press the F8 key, it sends a macro to:

Send {LButton}{LButton}
Send ^{Home}
Send {Shift down}
Send ^{End}
Send {Shift up}
Send ^c
return

Open in new window

which does the trick.

Thanks for providing the info on these three topics. I'm going to take some time to go through them and if I have any questions, I will post it as a new thread.  :)
0
 
Rory ArchibaldCommented:
If we can use function keys, then Application.Onkey will allow calling my code directly from one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.