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


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:

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
   End With
End Sub

Open in new window

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

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.)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve_BradyAuthor Commented:
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
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.
Steve_BradyAuthor Commented:
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

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.  :)
Rory ArchibaldCommented:
If we can use function keys, then Application.Onkey will allow calling my code directly from one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.