How do I programmatically double-click a cell in VBA/Excel?

Question

I have an Excel sheet with a formula that's only evaluated when the cell it's in is double-clicked. I have been trying to write some code in the open workbook event that triggers a double-click or VBA equivalent of a double-click to force an evaluation of this cell's formula but can't find anything that works. I've tried:

Application.Evaluate("I2")

^ Does nothing

With Range("I2")
            .Value = .Value
End With

^Also does nothing

Dim doubleClick As Variant
doubleClick = Application.WorksheetFunction.DfsCell("TimeSeries", 7.27, Chr(34) & "IRESS_UPDATE!$B$5:$B$36" & Chr(34), 2, , , , , , Chr(34) & "IRESS_UPDATE!$A$2" & Chr(34), Chr(34) & "IRESS_UPDATE!$B$2" & Chr(34), True, 0, False, True, "10:00:00", "16:00:00", 5, "0*5")

^Gives a Run-time error '438': Object doesn't support this property or method

Does anyone know how to simulate a double-click on a cell?

use `Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, ByVal Cancel As Boolean) Cancel = True End Sub` Event to determine dblclick on sheet — Ivan.s, Mar 21 '16 at 05:02
Is DfsCell some sort of third party RTD function or something? More information is needed. — , Mar 21 '16 at 05:04
To emulate mouse event use `Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)` [msdn](https://msdn.microsoft.com/en-us/library/windows/desktop/ms646260(v=vs.85).aspx) and `Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long` and `Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long` to obtain/emulate mouse coordinate — Ivan.s, Mar 21 '16 at 05:29
Jeeped - Yep it's a 3rd party RTD, or maybe DDE add-in, I'm not sure. — lachs, Mar 21 '16 at 06:11
Ivan.s - Getting the cursor position wouldn't work, I'd need the coordinates of the cell which I need to programmatically double-click. I think you're on the right track though, I'm pretty certain now that Excel/VBA doesn't have the function I'm looking for built in so I will probably need external libraries. I've been playing with OpenCV (image processing libraries) lately, maybe I could find the coordinates of the cell using that! — lachs, Mar 21 '16 at 06:11
the default built-in double click action of excel is to initiate cell editing. it make no sense to double click a cell to fire calculation because u'll be in cell editing mode. if u hv a 3rd party add-in that modified the default built-in action, can u call to the 3rd party DoubleClick precedure? — Rosetta, Mar 21 '16 at 06:38
As KS Sheon mentioned this question makes no sense. If you would like to insert formula through the VBA code in currently selected cell, use this: `ActiveCell.Formula="Dfs(...)"` — Maciej Los, Mar 21 '16 at 06:50
I add ALL function that you need to do to make your task. You must to calclulate cursor position of your cell in global coordinate to call `mouse_event`. — Ivan.s, Mar 21 '16 at 07:43
Your question is not clear. Why you need to click mouse in program. Why not use `.calculate`? [see](http://stackoverflow.com/questions/8342012/is-it-possible-to-recalculate-only-a-cell-or-a-range-in-vba) — Ivan.s, Mar 21 '16 at 07:51
My apologies guys, I overcomplicated this question. All I need to know how to do is programmatically enter a mouse double left click a cell in VBA. It seems that the 3rd party add-in is blocking automatic and programmatic evaluation of its formula, possibly to save bandwidth on the underlying web service that sends the data. I wasn't sure if this is what was happening before but it's become clear that normal functionality in VBA isn't working. — lachs, Mar 21 '16 at 23:04

lachs · Accepted Answer · 2016-03-22T22:29:58.407

I found a solution, the following code forces evaluation of the 3rd party add-in's functions, in this case the Australian financial markets software IRESS but I've seen examples with many add-ins:

Sub AutoUpdateIRESSData()
    Range("I2").Select
    Application.Run "'ddeiress.xla'!Execute"
End Sub

I added a reference to the above subroutine to the Workbook_Open event and it successfully re-evaluates the functions when the workbook is opened.

score -1 · Answer 2 · answered Mar 21 '16 at 09:34

-1

Does this work, changing the worksheet sub to be public rather than private?

Option Explicit

Sub test()

    Dim shToCall As Sheet1

    Set shToCall = Sheet1

    shToCall.Worksheet_BeforeDoubleClick Range("a1"), False

End Sub

answered Mar 21 '16 at 09:34

Nathan_Sav

8,466
2
13
20

Thanks for the input Nathan but I'm trying to add code to the Workbook_Open event so the formula in a specified cell will be evaluated when the workbook is opened, not before a doubleclick event is run. – lachs Mar 21 '16 at 23:50

How do I programmatically double-click a cell in VBA/Excel?

2 Answers2