Cancel = true not working in Worksheet_BeforeDoubleClick

SciTeacherEd

SciTeacherEd

New member
Joined
Feb 27, 2013
Messages
3
Reaction score
0
Points
0
If I understand it correctly, the Cancel parameter is supposed to prevent the Worksheet_BeforeDoubleClick action from exiting to edit mode. The problem is, I have included Cancel = True, but it still leaves me in edit mode.
I am using Excel 2010.
Any suggestions, or workarounds?

here is the code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim myRange As Range
Dim strMarker As String
Set myRange = Range("1:3")
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, myRange) Is Nothing Then
If Target.Value = 0 Then
Target.Value = 1
Else
If Target.Value = 1 Then
Target.Value = -1
Else
If Target.Value = -1 Then Target.Value = 0
End If
End If
End If
Cancel = True
End Sub
Click to expand...

It wouldn't really be a problem, except that I am cycling through 0, 1, -1, and with -1 I have to hit escape befor clicking another cell or it asumes I am entering a formula.

Thanks,
Ed
 
It works as it should for me.

BTW, you can simplify it a tad

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Dim myRange As Range


    Set myRange = Me.Range("1:3")
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, myRange) Is Nothing Then
        If Target.Value = 0 Then
            Target.Value = 1
        ElseIf Target.Value = 1 Then
            Target.Value = -1
        ElseIf Target.Value = -1 Then
            Target.Value = 0
        End If
    End If
    Cancel = True
End Sub
 
Bob Phillips said:
It works as it should for me.

BTW, you can simplify it a tad
Click to expand...

This is about as simple as it gets:

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Hi"
Cancel = True
End Sub

It still leaves the cell in edit mode for me. No idea why.

Ed
 
For the moment I have implemented a simple workaround of simply ending the code with
Code: 
Application.SendKeys "{ESC}"
which has the desire effect.
 
You must log in or register to reply here.
Back
Top