Private Sub keeps freezing file

[BrainDiseasee]

Hello, i have a code that is displaying the last changed cell of a range. It works for a few tests but then randomly will freeze up my workbook. Any help would be great, also im looking to make this apply to all worksheets in the workbook, if anyone has any direction on that it would be helpful as i am lost on how to make a privae sub work as a public sub/function.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A1:A10")
Application.EnableEvents = False
If Not Intersect(Target, Range("E5:E15")) Is Nothing Then


Range("J39") = Target.Address
Application.EnableEvents = True
End If

End Sub

 

[Joe4]

If you place this code in the "ThisWorkbook" module, it will apply to ALL sheets in your workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Intersect(Target, Sh.Range("E5:E15")) Is Nothing Then
        Application.EnableEvents = False
        Sh.Range("J39") = Target.Address
        Application.EnableEvents = True
    End If

End Sub

 

[BrainDiseasee]

If you place this code in the "ThisWorkbook" module, it will apply to ALL sheets in your workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Intersect(Target, Sh.Range("E5:E15")) Is Nothing Then
        Application.EnableEvents = False
        Sh.Range("J39") = Target.Address
        Application.EnableEvents = True
    End If

End Sub

That definitely solved the issue of getting to work on multiple sheets. However still having an issue with it crashing. My plan is to have a drop list from data validation, so when i go to my main sheet and try this it freezes up where i can t click on anything

 

[BrainDiseasee]

That definitely solved the issue of getting to work on multiple sheets. However still having an issue with it crashing. My plan is to have a drop list from data validation, so when i go to my main sheet and try this it freezes up where i can t click on anything

Disregard the freezing is something with in the actual drop list or something else because I disabled the code and it still froze. Thanks again!!!

 

[Joe4]

You are welcome.

Yeah, I was going to say that there isn’t really anything in that code that would cause it to crash, especially since we are disabling events while the update happens.