Automatically run VBA macro when cell value change

Question

I have a large workbook with many worksheets. Each worksheet contains a combination of user inputs and calculations and most link to inputs or outputs on other worksheets. The workbook also has a module that contains a number of custom VBA functions.

I'm trying to automatically goalseek a cell located on an unselected/non-active worksheet if the user changes a value on the active sheet that is linked to the input for the calculation on the other sheet that I'd like to goalseek. An example:

Cell A1 on "Sheet7" contains a number that is a "guess" for a calculation result based upon the values in cells A2, A3, and A4 of the same sheet (example, A1 is the notional result of A2 + A3 + A4). Cells A2, A3, and A4 are links to calculation results from three other worksheets. Cell A5, on "Sheet7", is the actual result of A2 + A3 + A4 and it may not match the value in A1. Cell A6 is the difference between the calculated result in A6 and the value in A1 and I'm goalseeking A6 to 0 to ensure that the calculated value in A5 always matches the 'hard coded' value in A1.

I would like this goalseek to happen anytime the value of A2, A3, or A4 changes on "Sheet7" even if "Sheet7" is not selected when the change occurs. A2 may be linked to "Sheet2", A3 may be linked to "Sheet14", and A4 may be linked to "Sheet9". If the user has "Sheet2" selected and changes something that changes the value linked to cell A2 on "Sheet7" I'd like this macro to run (reason being that the "Sheet7" A1 value is linked back to a number of other worksheets and it's important that it remains updated or the entire calculation chain fails across the workbook).

All of the macro examples/posts I found work if I have "Sheet7" selected and change A2, A3, or A4 but if I have any of the other worksheets selected and make a change that changes the linked values the macro will not run regardless of all the code variants I've tried.

I do not have a good handle on the overall "VBA architecture" so can someone provide an answer in understandable terms and some direction on the specific things I need to do or code to add to the module or worksheet(s) (or both).

Answer 1

You could use the "manual calculation"-trick.

The workbook simply is set manual calculation, then in "ThisWorkbook" do the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Application.EnableEvents = False
  Dim a As String
  a = Sheets("Sheet7").Range("A5").Value
  Calculate
  If a <> Sheets("Sheet7").Range("A5").Value Then
    'call your seek here
  End If
  Application.EnableEvents = True
End Sub

You need to change it to fit your needs, but it still should push you in the right direction ;)

Answer 2

I have noticed that in your comments you mentioned that you would like your macro to run whenever any specific cell changes on Sheet 7. If you just paste the following code inside Sheet 7 in your VBA Editor, it should do the trick (here, I assume you would like cell H5 to change. Once it changes, you should get the message "WORKS". If it does appear, then you did everything right and can just substitute the msgbox code with the one you need):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("H5")) Is Nothing Then
        MsgBox "WORKS"
    End If
End Sub