Event change error if different sheet activated

[conny2254]

I have been using the below macro to automatically add a semi colon when time is entered and it works great, but today I it errored. if I activate a cell within my named range "Time" and set the focus within the cell with the cursor and before I change the focus to a new cell I select another sheet within the workbook the macro bugs stating, "Method intersect of object_Global failed" Can anyone tell me what I could add to avoid this error? Thank you.

Sub EnterTime(Target)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Intersect(Target, Range("Time" & ActiveSheet.Index)) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        With Target
            If Not .HasFormula And IsNumeric(.Value) And .Value < 2400 Then
                .Value = TimeValue(Format(.Value, "00:00"))
            Else
                .Value = ""
                With ErrMsg
                    .Label1.Caption = "Error:" & vbNewLine _
                    & "The time entered was not valid"
                    .Show
                End With
            End If
        End With
    Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

What is calling that procedure?

You could try replacing:

ActiveSheet.Index

with:

Target.Parent.Index

 

[conny2254]

sorry this is where the procedure is called

Private Sub Worksheet_Change(ByVal Target As Range)
Call EnterTime(Target)
End Sub

 

[conny2254]

also I have named ranges Time1, Time2, and Time3 all relating to sheets(1), sheets(2), and sheets(3). so the only reason I use the activesheet.index is to refer to the correct named range based on what sheet is active. Im not sure if that needed to be specified or not

 

[Andrew Poulsom]

Did you try what I suggested?

 

[conny2254]

yes, I just did and that did work, now I just need to incorporate that with my other 2 event changes that are called, thank you!