VBA Copy and Paste

[Yoepy]

The following code does one of three things
1. Copies
3. Pastes
2. If copy and paste have already been done, the Double click transfers you to the event sheet.

I use a counter in "A1" to establish what we are doing (1 or zero)

My problem is, that if someone starts the process, with a zero and therefore is copying, if they hit the Esc key before the second double click (the Paste) the counter doesn't get reset.

How do I include
Range ("A1") = 0
if the ESC key is hit.

Thank you.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "B:B"
Const WS_DESTN As String = "G6:K68"
Dim WsName As String

On Error GoTo ws_exit
Cancel = True 'rid protection error

'Copy event
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
            If Range("A1") = 0 Then
                With Target
                    ActiveCell.Copy
                End With
            Range("A1") = 1
            End If
GoTo ws_exit

Else
    If Not Intersect(Target, Me.Range(WS_DESTN)) Is Nothing Then
    
'Go to the event
        If Range("A1") = 0 Then
            WsName = Target.Value
                With Target
                    Sheets(WsName).Visible = True
                    Sheets(WsName).Select
                End With
        Else
    
'Paste event
    If Range("A1") = 1 Then
                With Target
                    ActiveCell.PasteSpecial xlPasteValues
                End With
            Application.CutCopyMode = False
    End If
    End If
    End If
            Range("A1") = 0 'Reset counter
    End If

ws_exit:

End Sub

 

[footoo]

To disable the ESC key : Application.OnKey "{ESC}", ""
To enable the ESC key : Application.OnKey "{ESC}"

 

[Yoepy]

Thanks footoo, but I don't want to disable. I just want to reset my counter if the ESC key is used.

 

[footoo]

Try this :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "B:B"
Const WS_DESTN As String = "G6:K68"
Dim WsName As String


[COLOR=#ff0000]'On Error GoTo ws_exit[/COLOR]
Cancel = True    'rid protection error




'Copy event
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    If Range("A1") = 0 Then
        With Target
            ActiveCell.Copy
        End With
        Range("A1") = 1
    End If
    GoTo ws_exit


Else
    If Not Intersect(Target, Me.Range(WS_DESTN)) Is Nothing Then


        'Go to the event
        If Range("A1") = 0 Then
            WsName = Target.Value
            With Target
                Sheets(WsName).Visible = True
                Sheets(WsName).Select
            End With
        Else


            'Paste event
[COLOR=#ff0000]            On Error Resume Next[/COLOR]
            If Range("A1") = 1 Then
                With Target
                    ActiveCell.PasteSpecial xlPasteValues
                End With
                Application.CutCopyMode = False
            End If
[COLOR=#ff0000]            If Err > 0 Then Range("A1") = 0[/COLOR]
        End If
        Range("A1") = 0     'Reset counter
    End If
End If
ws_exit:
End Sub

 

[Yoepy]

Thanks again footoo, it works, but it still means the user needs to double click in the WS_DESTN range for the reset to happen.
It's more likely to be needed when they realise they have clicked the wrong one in the WS_RANGE and then ESC.

But I'll play with your idea.

 

[Yoepy]

A bit of playing with the order and I have it working.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "B6:B104"
Const WS_DESTN As String = "G6:K68"
Dim WsName As String

On Error GoTo ws_exit
Cancel = True 'rid protection error

'Copy event
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

            If Range("A1") = 1 Then 'Error handler
                Range("A1") = 0 'Reset counter
            End If

            If Range("A1") = 0 Then
                With Target
                    ActiveCell.Copy
                    Range("A1") = 1
                End With
            End If
GoTo ws_exit

Else

If Not Intersect(Target, Me.Range(WS_DESTN)) Is Nothing Then
'Go to the event
        If Range("A1") = 0 Then
            WsName = Target.Value
                With Target
                    Sheets(WsName).Visible = True
                    Sheets(WsName).Select
                End With
        Else
    
'Paste event
            If Range("A1") = 1 Then
                Range("A1") = 0 'Reset counter
                    With Target
                        ActiveCell.PasteSpecial xlPasteValues
                    End With
                Application.CutCopyMode = False
            End If
        End If
    End If
End If

ws_exit:

End Sub