How can i prevent overflow in this case

[spurs]

I have a worksheet with a few cells as shown in the attached jpg

I would like to run a macro whenever an entry is made to cells E2, F2, I2, or E4
in the jpg these are the cells with the current entries of 89 80 5.315 and 65.6

There is another cell not shown A1 in which a value 1 to 4 is input. There are 4 different calculation possibilities with the 4 cells
In each case any of the 4 cells can be calculated based on the values in the other 3 cells. A1 controls which way this calculation is done.

For example,
1) the values in F2, I2, and E4 could be used to calculate E2
2) the values in E2, I2, and E4 could be used to calculate F2
3) the values in E2, F2, and E4 could be used to calculate I2
4) the values in E2, F2, and I4 could be used to calculate E4

I was thinking of the following in the sheet module but this is causing overflow errors. The code only covers 3 scenarios so far but the stack overflow happens as soon as I go for more than one scenario

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
Call PrelimXaxisAngles
Else
If Not Intersect(Target, Range("F4")) Is Nothing Then
Call PrelimXaxisAngles
Else
If Not Intersect(Target, Range("I4")) Is Nothing Then
Call PrelimXaxisAngles
End If
End If
End If

In the first case if i change E2, depending on how A1 is set from 1 to 4, I could get PrelimXaxisAngles to write a value into any of F2,I2, or E4
I guess this triggers another worksheet event causing the overflow

Is there a recomendation on how to fix this so that we dont keep calculating in an endless chain?

 

[Micron]

I'm guessing the called sub edits the sheet? Then you are likely causing cascading changes because those changes keep calling the event you posted. For that, you turn off updating: Application.EnableEvents = False before doing anything that edits a sheet from a sheet editing event. I advocate using an error handler in case an error halts the sub and events is left disabled. In that case, the error handler ensures that execution returns to the exiting line label and that's where you put application settings back to normal. Please use code tags (vba button on posting toolbar) to ensure code remains indented and formatted. Like this

Sub StartHere()

On Error GoTo errHandler
Application.EnableEvents = False

'do code stuff

exitHere:
'put things back as they were, clean up object variables
Application.EnableEvents = True
Exit Sub

errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[spurs]

Thank you
This works great. You taught an old dog a new trick!

Best Regards

 

[Micron]

Old is relative (it's 20 years older than you, whatever number that is). Glad I could help.