Seeking Help with an Event Handler Function

[USNA91]

Hello, again...

So I have my workbook with 30+ sheets. Each sheet has the following code in it:

CURRENT CODE

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C7:L159")) Is Nothing Then
Range("C4").Value = Now
End If

End Sub

USER NEED

This code waits for any changes made in the cell range C7:L159 on that sheet, and when it detects such a change it drops the current date and time into cell C4 on that sheet. This function needs to be unique to each sheet. Simple enough.

PROBLEM

It works perfectly on one sheet, but throws errors on the others. Am I running afoul of an activation call or something?

Thanks.

 

[rabsofty]

whats the error? and what line is throwing the error?

 

[Domenic]

I'm not sure why you're getting those errors, but when the date and time is entered in C4, the event handler is triggered a second time. So I would suggest that you set EnableEvents to False before it's entered, and then back to true afterwards.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C7:L159")) Is Nothing Then
        Application.EnableEvents = False
        Range("C4").Value = Now
        Application.EnableEvents = True
    End If

End Sub

However, since you have the same event handler for each sheet, you can actually use a single workbook level event handler instead. For example, in the Visual Basic Editor (Alt+F8), select View, and then select Project Explorer. Then, for your workbook. right-click ThisWorkbook, and select View Code. Then enter the following code in the code module...

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

    If Not Intersect(Target, Range("C7:L159")) Is Nothing Then
        Application.EnableEvents = False
        Range("C4").Value = Now
        Application.EnableEvents = True
    End If

End Sub

If you continue to have problems, specify which error, and on which line, as @rabsofty has requested,

 

[USNA91]

DOH!

The error is question is 1004: "Application-Defined or Object-Defined Error". It occurs on the Range("C4").Value = Now line.

Going to read through Domenic's post now.

 

[USNA91]

I just realized something... This only seems to happen when the sheet is protected. The cell being updated via VBA (C4) is protected and should not be messed with by the user, but perhaps it is messing up the code?

 

[RobOrBob]

I think toggling Application.EnableEvents False then True is always good practice even though C4 is outside of the intersect range tested.

If that doesnt solve the problem, try qualifying the Range("C4").Value = Now with sheets eg
Sheets("Sheet1").Range("C4").Value = Now

 

[RobOrBob]

I just realized something... This only seems to happen when the sheet is protected. The cell being updated via VBA (C4) is protected and should not be messed with by the user, but perhaps it is messing up the code?

Yes. That would be the problem.

 

[Domenic]

Your code can be amended to deal with a sheet that may be protected. Note that you can omit the password argument, if no password is used to protect the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isProtected As Boolean
    isProtected = False

    If Not Intersect(Target, Range("C7:L159")) Is Nothing Then
        Application.EnableEvents = False
        If Me.ProtectContents = True Then
            Me.Unprotect Password:="MyPassword" 'change the password accordingly
            isProtected = True
        End If
        Range("C4").Value = Now
        If isProtected = True Then
            Me.Protect Password:="MyPassword" 'change the password accordingly
        End If
        Application.EnableEvents = True
    End If

End Sub

Hope this helps!

 

[RobOrBob]

Rather than update C4 directly you could have a hidden column (say XYZ) the cells within which are not protected.

Update XYZ4 in the code and have C4 itself set to a simple formula =XYZ4

 

[USNA91]

That's exactly what I ended up doing.

Thanks to all for the help!