Automated Macros when cell values change

[kripat-96]

Hi, I'm new to VBA, but for a project I'm working on, the calculations require a series of formulas such as goal seeks etc. I have enabled them such that the relevant macros are run when a value changes to a non-zero value. Examples of these;
Sheet 1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim found As Boolean
found = False
For Each cell In Range("AH106", "AH107").Cells
If cell.Value = "X" Then
found = True
End If
Next
If found = True Then
Call GoalSeek_T113
Else
Call GoalSeek_T113
End If
End Sub

Where the Macro is something along the lines of
Macro 1
Sub GoalSeek_T113()
Range("AH106").GoalSeek Goal:=0, ChangingCell:=Range("AL108")
Range("AH107").GoalSeek Goal:=0, ChangingCell:=Range("AK108")
End Sub

All of the formulas for the work sheet events and macros run fine, however because there are multiple sheets in my document, the problem is that when I click anywhere on the current sheet e.g. Sheet 1 that is when the macro designated by the sheet runs but if I was clicked on another page, the macro will not be run until I click back to Sheet 1. This is a problem as it means I still need to click through several sheets in a sequence. Is there any way to have the formula run when the value changes, regardless if I am clicked on the sheet or not?
Kind Regards

[TMS]

You have a Selection Change event handler rather than, what I think you need, a Change event handler.

Add to that, a Selection Change event handler will fire every time you select any cell on the worksheet. You should limit so that, although it will fire when a cell changes, it will only take action if it is one of the cells of interest.

Please Login or Register  to view this content.

[kripat-96]

Hi, thanks for the reply, from your description that sounds like exactly what I am looking for and what I want it to be able to do. I've tried running it, however it keeps coming up with a compile error, variable not defined and it highlights the 'Private Sub Worksheet_Change(ByVal Target As Range).

[TMS]

You're welcome. Thanks for the rep.


Add

Please Login or Register  to view this content.

[kripat-96]

Hi, still no luck, sorry.
I've tried adding it but it still always comes up with the compile error that the variable is not defined.
Is this because the value doesn't take the form of a range?

[TMS]

Please Login or Register  to view this content.

[kripat-96]

Hi, that formula works perfectly and I realised what I had to change for this to work was I changed it as 'If Not Intersect Is Nothing Then Exit Sub'.

I'm just having a little problem in combining it in a case where there are two seperate ranges that trigger two different macros, but both are on the same worksheet like this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "wsceh"

Dim rInterest As Range
Set rInterest = Range("AJ1122", "AK1122") & Range("AH998", "AH999")


If Not Intersect(Target, rInterest) Is Nothing Then Exit Sub

Dim cell As Range
Dim found As Boolean
found = False
For Each cell In Range("AJ1122", "AK1122").Cells
If cell.Value = "X" Then
found = True
End If
Next
If found = True Then
Call Goalseek_F101_A
Else
Call Goalseek_F101_A
End If


For Each cell In Range("AH998", "AH999").Cells
If cell.Value = "X" Then
found = True
End If
Next
If found = True Then
Call Goalseek_F101_B
Else
Call Goalseek_F101_B

End If

End Sub

This however didn't work so I'm not sure how to go about fixing the code to work with more than one macro to be run.
Sorry to bother you.
Kind Regards.

[TMS]

Untested, but something like:

Please Login or Register  to view this content.

It is NOT Not Intersect. It is saying, "if the intersection is nothing" exit the subroutine ... do nothing.

[BellyGas]

psst, kripat. put [ code ] [ /code ] tags around your code.

[kripat-96]

Hi, I think from what I've now understood is that the type of function I need is a VBA code where there is a change in the value by a formula, so I think it should be on something of a Worksheet_Calculate() event.
I'm trying to make it show that if the values in the range e.g. AH106 and AH107 are not equal to zero, the macro should be run (which makes their values zero), but I'm not sure about how to write any code that would work for this.

[kripat-96]

bump......

[TMS]

Sorry, I can't really second guess any further.

Post a sample workbook and describe the process that you are trying to undertake. Who changes what (cells), when, how, why, what happens then, what cells are updated, what results do you expect. Have you done this manually? What's the purpose of the redundant If ... Then ... Ele?

[kripat-96]

Hi, sorry for all of the confusion in my posts.
My actual spreadsheet is rather long and would be confusing so I made a very simple one to show what I need and how it currently works.
What I want is a way for both of the 'Difference' cells on the first page to equal zero.
Kind Regards

http://www.mediafire.com/file/19nd36...hu/Sample.xlsm

[kripat-96]

Update: Ok so I've updated the sample example and it mostly works.
Both of the Macros (GoalSeek_Sheet_1 and GoalSeek_Sheet_2) work completely fine, and The Worksheet_calculate event works perfectly on Sheet 1, where if the 'Factor' is changed, then the goalseek automatically runs to change the variable to make the difference equal to zero.

Now what I want is for sheet 2, I also want to set the difference to 0, which is done by macro GoalSeek_Sheet_2. I can't get a worksheet event for this though, as it always results in a crash. The difference is that the 'product' of the first sheet is used as the 'desired' value for the second sheet. I would like a way of changing only the factor in the first sheet, and automating the difference of the second sheet to be zero.

http://www.mediafire.com/file/th2ckm...With_Code.xlsm

Kind Regards

[kripat-96]

Bump........

[TMS]

Not everyone will download from a file sharing site and most would ask that you upload to the forum.

I followed the link and, when I tried to download the file, I got transferred to a betting site and a blank tab ... and that's why we don't like file sharing sites.

[kripat-96]

Sorry, I didn't even realize there was an option to attach it through here before.

[kripat-96]

bump......