Search 2 sheets for a date

[Ironman]

Hi

The below code searches Sheet 1 ('Training Log') for a date in Column 1.

Sub FindDate()
Dim myDt As Date
Dim myInput As Variant
Dim CellFound As String

If ActiveSheet.Name <> "Training Log" And ActiveSheet.Name <> "Training 1981-1997" Then
    MsgBox "The Locate Entry Date function will only run in Training Log", vbInformation, "Function Invalid in This Sheet"
Exit Sub
    Else
End If

    On Error GoTo Error1
myInput = InputBox("Enter Date Below: (d/m/yy)", "Locate Training Log Entry Date")
    If myInput = "" Then
        MsgBox "Search cancelled!", vbInformation, "Locate Training Log Entry Date"
        Exit Sub
    Else
 
    End If
 
    If IsDate(myInput) Then
        myDt = myInput
        CellFound = Application.Match(CDbl(myDt), ActiveSheet.Range("A:A"), 0)
        ActiveSheet.Range("A" & CellFound).Activate
        Exit Sub
 
    Else
 
    End If
 
Error1:
MsgBox "Sorry, no Training Log entry found for that date", vbInformation, "Search Unsuccessful"

End Sub

I'm looking for a small amendment to this so the code will also search the same column in Sheet 20 ('Training 1981-1997').

The code is also contained in Sheet 1 and Sheet 20 under

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

Can the amended code search Sheet 20 in Sheet 1 and vice versa, or can it only search the sheet that the code is in? If it won't do this, I presume it would need to be inserted in This Workbook?

Many thanks!

 

[Ironman]

If the active cell in Sheet 1 is in A1-A11 do not run the code
If the active cell in Sheet 20 is in A1 do not run the code

 

[Crystalyzer]

Question, should the doubleclick only run the code if the active cell is in column A excluding the cells mentioned above? If the active cell is in column C should it run?

 

[Ironman]

The code should only run when double clicking Sheet 1 Cells A12 onwards and Sheet 20 Cells A2 onwards, no other columns.

 

[Crystalyzer]

Place this code in the Sheet 1 vbe

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 And Target.Row > 11 Then
        Call FindDate
    End If
End Sub

Place this code in the Sheet 20 vbe

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 And Target.Row > 1 Then
        Call FindDate
    End If
End Sub

 

[Ironman]

Perfect!

Thank you so much!

 

[Crystalyzer]

Happy to help!