I am working with Excel/VBA and I have 4 inputs on Sheet1 (cells B2, B6, B7 and B8) that go into a calculation on Sheet2 (cell W8). Certain combinations of values entered into the 4 inputs on Sheet1 can cause the calculation on Sheet2 to go negative. In this case, I would like the value of the input on Sheet1 (can be caused by any one of the inputs) to revert back to the previous value just before the entered value caused the calculation on Sheet2 to go negative.
I know this cannot be done inline with Excel functions and requires some VBA coding. I already have a Worksheet_Change function set up so that when the Target.Address = any one of the 4 input cells, then it calls another function (NegX_Msg_Popup()) that checks if the calculation on Sheet2 cell W8 is negative and if so, gives a popup message but allows the entered value which caused the negative value to remain. I would like to keep the popup but have the value revert back to the last "good" value entered.
I tried to add a line in the NegX_Msg_Popup function which if Sheets("Sheet2").Range("W8").Value < 0 Then
Range("B2").Value = Range("B2").Value
but this put me into a circular reference and caused the workbook to hang up.
I have also tried on workbook open, creating a copy of the input worksheet thinking that if the negative condition was met that I would replace the active worksheet cell with the value from when the sheet first opened but then this only restores the original value when first opened but not if multiple changes are made before tripping the negative calculation function.
Any clever ideas on how I can achieve this?
Thanks so much!