Using Worksheet Change when range is updated by formula not user

[winspear]

Hi

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.

Any ideas? Your help would save my sanity!

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 _
:=True
 
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 _
:=True
 
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

 

[VoG]

You would need to use the Worksheet_Calculate() event.

 

[winspear]

Thanks for the advice.

I would like to execute the following within Worksheet_Calculate

Check 1 - If Range 'TCRunningFlag' changes to 2 then execute code

Check 2 - If Range 'notRunCount' changes to 0 then execute code.

I would only like the code to execute when the ranges change

I also cannot see how the checks can be executed independently of each other.

The draft code follows......



Private Sub Worksheet_Calculate()

If Range("TCRunningFlag").Value = "2" Then
If Range("actualStartDate").Value = "" Then
Range("actualStartDate").Value = Range("Now").Value
MsgBox ("Test case execution started" & vbCrLf & "Actual Start Date populated with " & Range("Now")), vbInformation, "EzTest - Execution started"
End If
End If
Exit Sub

If Range("notRunCount").Value = "0" Then
If Range("actualEndDate").Value = "" Then
Range("actualEndDate").Value = Range("Now").Value
MsgBox ("Execution complete" & vbCrLf & "Actual End Date populated with " & Range("Now")), vbInformation, "EzTest - Execution complete"
End If
Exit Sub
End If

End Sub