Worksheet_Change code for multiple ranges

[winspear]

Hi

I have the following code in my Worksheet_Change module - each piece of code works fine, however, from time to time I am receiving an out of stack space error in Excel during execution.

I do not think this code is formatted correctly for efficient use of the Worksheet_Change event (for example, it does not use:
Set isect = Application.Intersect(Target, Range("designStatus"))
if Not isect is Nothing Then

Having read message boards and tried out variations of this 'Intersect' code, I cannot the appropriate section of code to execute when the relevant range is changed.

Any ideas how to reformat this code to make it more efficient and avoid the 'out of stack space' error?

Any help is so much appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Answer As String

'1st check - if designStatus = Complete

If Range("designStatus").Value = "Complete" Then

If Range("designCompleteDate") = "" Then

If Range("missingVPCheck").Value > 0 Then
Answer = MsgBox("A sanity check of this test case indicates that you have used" & vbCrLf & "the words 'Verify' or 'Confirm' in a description field" _
& vbCrLf & "but the 'Type' = Step" & vbCrLf & vbCrLf & "If you would like to continue, select 'Yes' else" _
& vbCrLf & "select 'No' and return to the test case to make amendments", vbYesNo, "EzTest - Verification Point check failed")


If Answer = vbNo Then
Exit Sub
End If


End If

If Range("missingVPCheck").Value = 0 Then


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"

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"

Exit Sub

End If

End If

Range("designCompleteDate").Value = Range("Now").Value
MsgBox ("Design Complete Date populated with " & Range("Now").Value), vbInformation, "EzTest - Design Complete"

End If
'2nd check - has execution started? if so populate the Actual Start Date

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

'3rd check - is execution complete> if so, populate the Actual End Date
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
End If


End If

End Sub

 

[wolverineb]

Maybe try sandwiching any code that changes values on the sheet between these two statements.

The first one tells the sheet not to run the worksheet_change procedure when you make the following change(s) to the sheet - basically to avoid an infinite loop

Application.EnableEvents = False 
'Put your statements that change things here
Application.EnableEvents = True 'turns change recognition back on

HTH