Exit / disable edit mode after double-click event

Question

I want to display a dialog after a user clicks on a cell in an Excel worksheet. Something like that:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) MsgBox "a cell was clicked!", vbOKOnly, "a click" End Sub 

It works great. The problem is that after you enable the double-click edit mode, it is expected that the formula will be entered. How to disable this behavior?

I would like to get pure functionality: ~ the user clicks the cell A dialog box appears ~ the user closes the dialog ~ the cell does NOT go into edit mode, the sheet looks exactly the same as before the double-click event.

Answer 1

You must undo the action with the variable specified in the argument:

 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) MsgBox "a cell was clicked!", vbOKOnly, "a click" 'Disable standard behavior Cancel = True End Sub 

Here is an example of a mannequin:

 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim response As Variant response = MsgBox("Are you sure you want to edit the cell?", vbYesNo, "Check") If response = vbYes Then Cancel = False Else Cancel = True End If End Sub 

Please note that you do not need to set Cancel to False , because this is the default value (this is for example).