0

I am trying to code my spreadsheet to react to changes to a specific cell in my spreadsheet. This cell contains a formula so the programing is not recognizing any change to the cell although the number is updating the formula is not. I am looking for a way to return the results of the cell containing the formula into another cell as a value so the change can be recognized by the code.

braX
  • 11,506
  • 5
  • 20
  • 33
Katie Stratton
  • 1
  • If you use a worksheet_calculate event handler you can monitor your cell from that. Eg see this post: https://stackoverflow.com/q/44127072/478884 – Tim Williams Jul 29 '22 at 00:52
  • Could you share the formula and explain **which cells containing values** in which worksheet of which workbook make the formula change the result? Depending on this information, there still may be a `Worksheet_Change` solution using [Range.Precedents](https://learn.microsoft.com/en-us/office/vba/api/excel.range.precedents). Also, please share your current code even if it is `Worksheet_Change`. You can [edit your post](https://stackoverflow.com/posts/73160420/edit) at any time. – VBasic2008 Jul 29 '22 at 01:59

1 Answers1

0

The change event isn't firing because the contents of the cell aren't changing, just what it displays (the formula result) is. You could use the Worksheet_Calculate event and check the value against another static value. If it's changed, then update it and trigger your other code.

It sounds like there's a better way to design your sheet though.

Private Sub Worksheet_Calculate()
    Dim watchCell As Range ' set to something
    Dim checkCell As Range ' set to something
    
    If checkCell.Value = watchCell.Value Then Exit Sub
    
'   Value has changed. Update the check and trigger action.
    checkCell.Value = watchCell.Value
    Call SomeOtherResponse
End Sub
SSlinky
  • 427
  • 2
  • 9