Results 1 to 4 of 4

Thread: Cancel = true not working in Worksheet_BeforeDoubleClick

  1. #1

    Cancel = true not working in Worksheet_BeforeDoubleClick



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    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

  2. #2
    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

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    It works as it should for me.

    BTW, you can simplify it a tad
    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

  4. #4
    For the moment I have implemented a simple workaround of simply ending the code with
    Code:
    Application.SendKeys "{ESC}"
    which has the desire effect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •