CODEWITHSUNDEEP
vbaexcelvsto
Prazeodyn
Prazeodyn

Reputation: 1

Excel double click on a ribbon button to initiate macro

I have a seemingly simple problem in Excel 2013 to which I cannot find an answer. Below I described all the details. Big thanks in advance for any help.

Circumstances surrounding the problem:

  1. I made a "QuickMacro.xlsm" workbook in Excel that contains Macro1.
  2. Secondly I added custom ribbon (through excel tools, not in VSTO) and on that ribbon I added said macro.
  3. Now every time I open an excel workbook I have a quick accces to that macro across all workbooks on my computer (and the workbook with said macro is hidden, so I do not see it and it does not interfere with my work)

My problem: I want Macro1 to initiate only when I double click its icon on the ribbon. Single click ought to do nothing.

My research on the problem: To find an answer I have went through many msdn materials on VSTO add ins and macros tutorials. The problem with former is that I am only a beginner in VSTO, and with latter that I find only "Worksheet_BeforeDoubleClick" events that regard cell activation, not ribbon button activation.

Question: Finally, my question is: how can it be done? Is there a way to do it with Excel only or do I need to start learning advanced VSTO? Also, I would also appreciate any general tips on where to start digging to solve this problem (except msdn sites which I have analyzed extensively). Any books maybe?

Upvotes: 0

Views: 677

Answers (1)

Tim Williams
Tim Williams

Reputation: 166860

Tested by assigning this to a shape on a worksheet (so your Sub declaration may look different).

Sub MenuAction()

    Static t As Double

    '0.25 sec double-click interval
    If [NOW()] - t > (1 / 24 / 60 / 60 / 4) Then
        t = [NOW()]
        Exit Sub
    End If

    Debug.Print "double-click"
    'run your code here

End Sub

EDIT: a side note on use of [NOW()] vs. Now(). I saw a post saying that these two expressions have different precision and that [NOW()] is more precise.

For example (also included Time for comparison):

Sub Tester() Dim i, x

For i = 1 To 10
    'make a delay...
    For x = 1 To 3000#
        DoEvents
    Next x
    Debug.Print Time * 1, Now() * 1, [NOW()] * 1
Next i

End Sub

Output:

 Time                        Now()                       [NOW()]
 0.494270833333333           42891.4942708333            42891.4942753472 
 0.494270833333333           42891.4942708333            42891.4942768519 
 0.494270833333333           42891.4942708333            42891.4942782407 
 0.494270833333333           42891.4942708333            42891.4942795139 
 0.494270833333333           42891.4942708333            42891.4942810185 
 0.494282407407407           42891.4942824074            42891.4942824074 
 0.494282407407407           42891.4942824074            42891.494283912 
 0.494282407407407           42891.4942824074            42891.4942851852 
 0.494282407407407           42891.4942824074            42891.4942865741 
 0.494282407407407           42891.4942824074            42891.4942880787

Interesting!

Upvotes: 1

Related Questions