0

I have the following code that I like to add to the BeforeDoubleClick event for Sheet3 from code in a module in the same workbook. It looks like I can do something like this but that involves doing .InsertLines for each line of my code. Is there a better way?

Look like I can build a string of my code as well which feels more straightforward. I only have 41 lines so I guess it's not so bad.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim acct As String
Dim month As String
Dim ws2 As Worksheet
Dim lastRow As Long

Cancel = True
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

With Target.Worksheet
    acct = .Cells(1, Target.Column).Value
    month = .Cells(Target.Row, 1).Value
End With

With ws2

    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

    For i = 1 To lastRow
        If .Cells(i, 3).Value = acct Then Exit For
    Next

    For k = i To lastRow
        If .Cells(k, 6).Value = month Then Exit For
    Next

    For m = k To lastRow
        If .Cells(m, 6).Value <> month Then
        s = m
        Exit For
        End If
    Next

    ws2.Activate
    .Range(.Cells(k, 10), .Cells(s - 1, 12)).Select
    Selection.Activate

End With

End Sub

Solved: All I had to do was add

If Sh.Name <> "Sheet3" Then Exit Sub

to the top of my code. Thank you to SJR.

Community
  • 1
  • 1
zero
  • 17
  • 5
  • You could try putting the code in the `Workbook_SheetBeforeDoubleClick` event in the `ThisWorkbook` module which has an additional sheet argument. – SJR Nov 02 '17 at 16:21
  • Can you elaborate? The code in my module creates 2 new sheets including Sheet3 which I want the above code inserted after it's created. – zero Nov 02 '17 at 16:26
  • The sheet argument is just so the `Workbook_SheetBeforeDoubleClick` event knows which sheet fired it? How will that transfer my code to Sheet3? – zero Nov 02 '17 at 16:29
  • If you place the above code in the ThisWorkbook module, it will apply to any sheet in the workbook so that you don't need to add it to specific sheet modules (though you can exclude particular sheets by amending the code), including any sheets added via other code (though I think only once the procedure has finished). – SJR Nov 02 '17 at 16:33
  • 1
    Ah I see what you mean now. That sounds like it could work thanks! – zero Nov 02 '17 at 16:37

0 Answers0