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]

Private Sub Worksheet_Calculate()
If Range("N9") = Somthing then Goalseek123
End sub</pre>

 

[RSXchin]

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.

That's why I had him put it in the ThisWorkbook module. If anything is changed (the cells that feed the formulas for example) then the code will fire.

 

[Smitty]

OK, let's cover a few VBA basics.

Modules are nothing more than containers for code, but there are different kinds of modules. When you open the VBE, the project explorer on the left, which shows all open workbooks, Add-ins, etc.

In a new workbook you would see: VBA PRoject (Book1), with a folder: Microsoft Excel Objects beneath it. There you see your worksheets and the ThisWorkbook module.

Worksheet level code, like the Worksheet_Calculate event goes in the worksheet specific module. So if you want code to monitor sheet1 for changes, then your Change event code goes in the Sheet1 module.

Workbook level code goes in the ThisWorkbook module and nowhere else.

Then you have General or Standard Modules. This is where the rest of your code goes, like recorded macros, your Goalseek code, etc. Those will be in a folder beneath the ThisWorkbook module.

There are also UserForm & Class modules, but you dont' need to know about them yet.

 

[yichuansancun]

thanks for the education

so I right click the current worksheet and click "view code", that will bring me to the current worksheet in Visual Basic, and I pasted the following codes:

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

hit "save", still nothing happens when the value in N9 changes. I have to run the macro Gaolseek123 manually...

should I give up?

 

[RSXchin]

you have to close the workbook and reopen it.

 

[Smitty]

thanks for the education

so I right click the current worksheet and click "view code", that will bring me to the current worksheet in Visual Basic, and I pasted the following codes:

The Workbook_Open event needs to go in the ThisWorkbook module, not the sheet module.

 

[yichuansancun]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Dear RXNchin and Smitty,

I placed the following codes into ThisWorkbook:

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

Then the codes of macro Goaseek123 are as follow:

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

IT WORKED!!!!!!!!


Thank you for all your help!!!! You guys are awesome!!!


Perri