CODEWITHSUNDEEP
excelvba
Adam.Butler
Adam.Butler

Reputation: 1

BeforeDoubleClick macro trigger not working in excel 2010 VBA

I am trying to trigger a macro in excel 2010 using the BeforeDoubleClick procedure. However, it does not appear to be working. I have searched extensively to see if other people have been having the same problems, a couple have but I have tried their solutions and it either wasn't applicable or didn't work. The code is very simple as I have stripped it back in order to test it.Is there something foolishly wrong with my code? (I am a Stack and VB noob so please be patient)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Value = "GO" Then Cancel = True
    MsgBox ("done")
End Sub

I have tried setting it to public sub, putting "Application.EnableEvents = True" in the immediate window. Workbook is set to enable all macros. No add ins are running. I have been through the XLSTART folder and its all empty. Even created a digital signature at one point as someone suggested it. Could it still be some from of compatibility issue with previous versions of excel?

Also when you do double click in the work sheet even though cancel = True the cell just opens up for editing as normal.

Please Help

Upvotes: 0

Views: 6756

Answers (1)

MikeD
MikeD

Reputation: 8941

The code you posted, when entered in a sheet's local module (e.g. Sheet1) will work without flaws. From the point of user experience you hardly notice any difference, namely

  1. if you double click a cell with value "GO", you see the [Done] msgbox, and after quitting this you are left with the cell selected but not in edit mode (i.e. insert cursor not in cell)

  2. if you click a cell with any other value (e.g. "abc", or even an empty cell), you see the [Done] msgbox, and after quitting this you are in cell edit mode with the write cursor placed after the last letter of the current value, so you can immediately start typing into the cell.

Having said that, even in 1. above nothing prevents you to start typing. By press of the first letter your "GO" is entirely replaced by the single letter you typed - so the difference for the user is minimal and could well be overlooked, unless your final code does a bit more like repositioning the selection, coloring the cell etc.

Upvotes: 1

Related Questions