Before Double Click triggering a second time after End Sub

Question

This code executes twice and its not supposed to! I have no idea why its being triggered twice.

Option Explicit
'Double click within pivot table to "select" savings scenario for each row and paste into "Custom Scenario" columns
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim LastRow As Integer
Dim ws As Worksheet
Dim wb As Workbook
Dim i As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)

LastRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Select Case Target.Column 'Basically and If Else, using this value to test criteria
    Case 7, 8, 9, 10, 11, 12, 13, 14, 15, 16 'Only do stuff when in these columns
        If Not Intersect(Target, Range(Cells(10, Target.Column), Cells(LastRow, Target.Column))) Is Nothing Then 'Only do stuff when in these rows of the column
            Select Case Target.Column
                Case 7, 9, 11, 13, 15 'Units columns
                    ws.Cells(Target.Row, 18) = Target.Value 'This column has units
                    ws.Cells(Target.Row, 19) = Target.Offset(0, 1) 'Next column has savings
                    ws.Cells(Target.Row, 17) = Cells(8, Target.Column) 'Percent
                Case Else 'Savings columns
                    ws.Cells(Target.Row, 18) = Target.Offset(0, -1) 'Previous column has units
                    ws.Cells(Target.Row, 19) = Target 'This column has savings
                    ws.Cells(Target.Row, 17) = Cells(8, Target.Column).Offset(0, -1) 'Previous column has the percent
            End Select
        End If
End Select

Application.EnableEvents = True

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 8 Then ThisWorkbook.RefreshAll
End Sub

I can step through it one line at a time and see that it is actually going from the End Sub Statement back to the Pub Sub Worksheet_BeforeDoubleClick statement. Makes no sense. Thanks in advance!

Edit: I should also mention I am double clicking on a pivot table. Which I know usually brings up a new tab with the data, but Ive used this event before on the same pivot table and its worked no problem. Not sure why this code is different.

Side note: `Case 7, 8, 9, 10, 11, 12, 13, 14, 15, 16` -> `Case 7 to 16`. — BigBen, Mar 05 '20 at 15:14
Also, [don't use `Integer`, use `Long`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Mar 05 '20 at 15:14
@BigBen Yes I know I should be using Long especially for the row variable, but in this case my row isnever more than 40. but I will change it anyway! Also I know what the case statement is doing, it works as intended, not the reason its kicking off twice. — mason howard, Mar 05 '20 at 15:19
Yes - they are both side notes... but as that question says, you really *shouldn't* use `Integer`. — BigBen, Mar 05 '20 at 15:20

score 0 · Answer 1 · answered Mar 05 '20 at 15:31

0

SOLVED: Added "Cancel = True" after "EnableEvents = true."

answered Mar 05 '20 at 15:31

mason howard

3
2

Before Double Click triggering a second time after End Sub

1 Answers1