how to get the value when date field is changed?

Worksheet change when target is updated by formula not user

• - I am hoping I can draw on your fantasticVBA experience to answer this conundrum! I have a problem in using the Worksheet Change event. My code checks the Target range named 'designStatus' and performs various checks and actions if the user changes this cell. My problem comes if the Target range does not equal 'designStatus'. In this instance, I would like the system to check if the Target range is a range named 'testCaseStatus'. The range 'testCaseStatus' is not directly updated by the user but is updated as a result of a formula. In this case I cannot get the Worksheet Change event to fire when testCaseStatus is updated due to this formula, instead it displays "Nothing changed in range" Message Box I have checked and the event does work if I reference the individual cell, however, I cannot use the individual cell reference as it is not static. Code below: Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String '1st check - if designStatus = Complete If Target = Range("designStatus") Then MsgBox ("designStatus changed") If Range("designStatus") = "Complete" Then MsgBox ("designStatus = Complete") If Range("designCompleteDate") = "" Then MsgBox ("designCompleteDate = blank") If Len(Range("testCaseName")) = 0 Or Len(Range("risk")) = 0 Or Len(Range("likelihood")) = 0 _ Or Len(Range("wireframe")) = 0 Or Len(Range("testType")) = 0 Or Len(Range("testSubType")) = 0 _ Or Len(Range("complexity")) = 0 Then MsgBox ("You must ensure values are entered in the following fields before you can continue:" _ & vbCrLf & vbCrLf & "Test Case Name" & vbCrLf & "Estimation Execution Time" _ & vbCrLf & "Risk" & vbCrLf & "Likelihood" _ & vbCrLf & "Wireframe (value or N/A)" & vbCrLf & "Test Type (value or N/A)" & vbCrLf _ & "Test Sub-Type (value or N/A)"), vbCritical, "EzTest - Mandatory Field(s) not complete" ActiveSheet.Unprotect Range("designStatus").Value = "Error" Range("designStatus").Interior.ColorIndex = 3 Range("designStatus").Font.Bold = True Cancel = True MsgBox ("Test Design Status set to Error"), vbInformation, "EzTest - Resolve Errors" ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _ Exit Sub End If ActiveSheet.Unprotect Range("designCompleteDate").Value = Range("Now").Value MsgBox ("Design Complete Date populated with " & Range("Now").Value), vbInformation, "EzTest - Design Complete" ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _ Exit Sub End If End If Else '2nd check - Has 'testCaseStatus' range changed? If Target = Range("testCaseStatus") Then MsgBox ("testCaseStatus changed") Exit Sub Else MsgBox ("Nothing changed in range") Exit Sub End If End If End Sub

• Answer:

  First, I believe your If tests are flawed. An IF test like If Target = Range("designStatus") Then is actually this: If Target.Value = Range("designStatus").Value Then If the value in designStatus is always going to be unique, then that is a legitimate test, but say it has a value like "complete" and "complete" can be found in other cells. Then you could get a false match and you code could act when it isn't appropriate. it you want to know if designStatus triggered the Change Event, then you would use If Target.Address = Range("designStatus").Address Then This may solve your other problem since you say a hard coded reference was working. However, if the change is cause by a calculation, I suspect that if it worked, it was coincidence rather than by design. A change to a cell containing a formula (caused by the recalculation of the formula) does not trigger the change event. The calculation triggers a calculation event and that has no target argument - so you have no way of knowing what action triggered the calculate. A work around I have seen is to trap the cell that is actually changed and would cause testcasestatus to change if tastcasestatus is dependent on cell B9, then rather than if Target.Address = Range("Testcaststatus").Address Then you would use If Target.Address = "$B$9" then if the formula is dynamic, then you can check the directprecendences property I set up a little testcase worksheet that had the defined name Testcasestatus. That cell had a formula like =A1+C1 I then built this change event: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TestCaseStatus").DirectPrecedents) Is Nothing Then MsgBox "TestCaseStatus has changed" End If End Sub and it signalled when A1 or C1 was edited. That would support a dynamic TestCaseStatus location and a formula where references might change. Now that isn't going to work with something like the Indirect function, but I will assume that you are not using that. Post back if that didn't answer the question.