0

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).

Improve this question
6
  • you can't go for a change which is just a the calculation of a formula. having =A1 in A2 it will never trigger A2 if it changes... only user-input triggers the change (or VBA itself)... so you need to keep track of the active sheet and changes cell to know what to look for... Commented Jan 28, 2016 at 2:45
  • also... you can write the worksheetchange in the worksheet (only counts for that sheet) or in the ThisWorkbook-module to do it for all sheets... but as said, just a differend outcome of a formula wont trigger it. And even having a lot of text here, I still do not get your real problem :/ Commented Jan 28, 2016 at 2:49
  • The fundamental problem is that I'm trying to work around a circular reference. The example I provided was to try, perhaps unsuccessfully, to provide a simple example of what I was trying to do. In reality it's much more complex (i.e. I don't have cells A2, A3, and A4 linked from other sheets that are being used in a calculation, there are dozens). Commented Jan 28, 2016 at 3:02
  • ...and it would be extremely difficult to try to identify the origin of each link and link the macro to each of the 'source' cells. By linking all the other sheet outputs to this single sheet ("Sheet7" in my example) and performing the final calculation here and then goalseeking between the "guess" and the calculated value it makes the result much easier to follow. I just need help figuring out how to trigger the macro to always run if any of the values on "Sheet7" change. Commented Jan 28, 2016 at 3:08
  • ...but it sounds like you're saying this is not possible to do? I also tried placing a sub on "Sheet7" to call "Macro1" located in a workbook module (and then had the goalseek code in the Module) but this didn't work either. Commented Jan 28, 2016 at 3:09

2 Answers 2

Reset to default
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 ;)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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
Improve this answer

2 Comments

Thanks for the all the help so far! In response to the last post, if I do this everything works perfectly as long as "Sheet7" is the active sheet and the user physically types a new value into cell "H5" (referencing your example code). However, the equation in cell "H5" is something like "=Sheet2!A1" where cell A1 on Sheet2 is the actual user input cell. If I navigate to Sheet2 and change the value in cell A1 the linked cell will change on Sheet7 but the macro does not run. The only way to get it to run is to have Sheet7 selected and type a value in cell A1. Does that make sense?
Unfortunately, it is not going to work either, as the Target defines your currently selected and amended cell. It is because of this reason that this macro works only if "H5"on sheet 7 is selected AND amended. Linked cells do not make it work. Why don't you consider transferring all the input cells in one single worksheet? It will allow you organise your data, make it easier for you to follow the input part and, more importantly, make this macro work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.