Detecting changes to Worksheet Cells

Question

I have a worksheet that is protected, so users can only enter data in certain cells.  I want to be able to detect when a cell is changed and run a macro that uses GoalSeek to perform a calculation. 

 The sheet is unprotected during the calculation, data from a cell is placed in a "holding cell", the calculation is performed and the result is stored in another cell, and then the original data is replaced.

Here's the code I have in place.  My problem is that it either doesn't detect the change and so doesn't calculate, or it does and gets caught in an endless loop.  I added several unprotects because it seems to re-protect the sheet (maybe due to another macro).

Private Sub Worksheet_Change(ByVal Target As Range)

   Calculate

   ActiveSheet.Unprotect

   Range("A243").Value = Range("B135").Value

   Range("D171").GoalSeek Goal:=10000, ChangingCell:=Range("B135")

   ActiveSheet.Unprotect

   Range("B243").Value = Range("B135").Value

   ActiveSheet.Unprotect

   Range("B135").Value = Range("A243").Value

   ActiveSheet.Unprotect

   Range("C185:D185").Value = Range("C243").Value

   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

   Calculate

   End Sub

Thank you in advance for your help.

Answer 1

When changing cells in a Worksheet_Change event, it is always a good idea to disable events before changing any cell. Just remember to enable events again before End Sub.

Application.EnableEvents=False 'Disable events

'Code to change cells

Application.EnableEvents=True 'Enable events

Regards,

Per

Answer 2

OK with a little playing you could try this... based on the assumption that D243 is available as a holding cell and you point the equation references in D171 from B135 to D243...

I have also assumed that this code is in the sheet module so I have used the Me object.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$135" Then

        With Me

           .Unprotect

           .Range("A243").Value = .Range("B135").Value

           .Range("D243").Value = .Range("B135").Value

           .Range("D171").GoalSeek Goal:=10000, ChangingCell:=.Range("D243")

           .Range("B243").Value = .Range("B135").Value

           .Range("C185:D185").Value = .Range("C243").Value

           .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

        End With

    End If

End Sub

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 3

Rik and Jessen,

Thank you both very much.  I think both solutions are elegant and work.  I took the path of least resistance and applied Jessen's Application EnableEvents command to my original formula, and that solved the problem.  It seems that it got caught in the loop because the macro was making changes to the sheet and triggering and re-triggering the Worksheet Change macro.  By disabling the events it stops this from happening until after the macro is complete.  Thanks for your patience with a Noob.

Regards.

Answer 4

Thanks for validating that I'm not crazy Rik.  Do you have any suggestions for other ways I can detect cell changes and execute this calculation?  I'm a noob, so feel free to overexplain.

Thank you.

Answer 5

Hi there,

Yep definitely an endless loop... you will need to try a different event...

when you change a cell in code in the sheet it re-triggers the event, so you are never passing A243=B135.

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.