How to automatically triger Goalseek macro?

[yichuansancun]

I have the following code:

Sub Goalseek()

Range("$N$7").Select
Range("$N$7").Goalseek Goal:=Range("$N$9").Value, ChangingCell:=Range("$N$3")

End Sub

How can I make excel to run this macro automatically every time there is a change in cell N9's value?

Thanks,
Perri

 

[yichuansancun]

N9 contains a formula

G21+G15

Both G21 and G15 contain formulas as well.

 

[RSXchin]

try something like this (This goes in your workbook module)

Public N9 As String
Private Sub Workbook_Open()
    N9 = range("N9")
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As range)
    If range("N9") <> N9 Then 
        Goalseek123
    End If
End Sub

ALSO, don't forget to add Application.EnableEvents = False to the beginning of your Goalseek123 and Application.EnableEvents = True to the end of it.

 

[RSXchin]

Sorry
change that to

N9 = range("N9").Value

 

[yichuansancun]

You mean this?

********************
Sub Goalseek123()

Application.EnableEvents = False

If LCase(Range("education_selection").Value) Like "*mercer data*" Then
Range("education").ClearContents
End If

If LCase(Range("choice").Value) Like "*specified amount*" Then
Range("housing_selection").ClearContents
ElseIf LCase(Range("choice").Value) Like "*mercer data*" Then
Range("housing").ClearContents
End If

Range("$N$7").Select
Range("$N$7").Goalseek Goal:=Range("$N$9").Value, ChangingCell:=Range("$N$3")

Application.EnableEvents = True

End Sub

 

[RSXchin]

yeah... but did the rest of it work?

 

[yichuansancun]

No it didn't

I have the exactly the followings:
Public N9 As String
Private Sub Workbook_Open()
N9 = Range("N9").Value
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("N9") <> N9 Then
Goalseek123
End If
End Sub

What do you mean by Module? The above code is not shown as an individual module, but shown when I click Sheet3 under Microsoft Excel Objects.

Do I need to create a new module under Modules folder?

 

[Smitty]

when I type MsgBox in front of Goalseek123, it gives the error message "compile error" again

As it should.

I meant like this:

Private Sub...
  .Range(N9)...
  msgbox "foo"
  ' everything else
end sub

You just use the message box as a quick way of testing if the code is firing or not, but it needs to be on its own line.

 

[RSXchin]

Put this in the "ThisWorkbook" module

Public N9 As String
Private Sub Workbook_Open()
    N9 = Sheets(3).range("N9").Value
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As range)
    If Sheets(3).range("N9") <> N9 Then Debug.Print "N9 has changed since the workbook opened"
End Sub

 

[Smitty]

If N9 is the result of a calculation, a change event won't do anything. You'd need to change it to a Calculate event instead.

Private Sub Worksheet_Calculate()
  If Range("N9") = Somthing then Goalseek123
End sub

 

[yichuansancun]

Not working

I have the codes you just typed under "ThisWorkbook" under the folder "Microsoft Excel Objects"

There is another folder named with "Modules" but under this folder there is no "ThisWorkbook".