Excel VBA Multiple Automatic Goal Seek Required

BobBridges · #8 06-21-2014, 09:29 AM

Step two: Calling a common module from each of the worksheets.

You already have a start because you have a Worksheet_Change subroutine written in one of your worksheets:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub

I'll show you how to change that around so this subroutine calls another one. Here's a subroutine that I named DoGoalSeek with all those statements copied into it almost unchanged:

Code:

Sub DoGoalSeek(Target)

Dim r As Range

Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub

This code I write into the new module you just created. Now it can be called by any other subroutine in your workbook, like this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
DoGoalSeek Target
End Sub

Here's what happens: When you change any cell in a worksheet, Excel runs the Worksheet_Change subroutine for that worksheet (if any). The only thing that subroutine does, as I've modified it above, is call DoGoalSeek and pass it the Target object (which is a pointer to the cell that was changed).

DoGoalSeek takes the Target object passed to it and does everything that used to be in your Worksheet_Change subroutine. When it's done, it passes control back to Worksheet_Change, which returns control back to you.

Now you can paste the same three-line subroutine into Sheet2, and into any other price sheet you have in your workbook, and each one will call the same logic every time a cell is changed. Then when you have to change the way DoGoalSeek works—and we will change it, in several ways—you can change it in just one place instead of in every worksheet.

Next post: What we have to do to DoGoalSeek to make it work for any row, not just row 2.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
automatic entering of multiple words	cliff@tauruscommercial.ca	Word	5	06-03-2014 06:06 PM
set daily goal for actual workdays, excluding weekends holidays and days off	Brian Reilly	Excel	1	01-24-2014 11:56 PM
Excel 2007 - formula or macro/vba code required	wrighty50	Excel Programming	3	05-13-2012 02:24 PM
Adding a goal line to a chart	uhlersa	Excel	1	01-16-2012 08:18 PM
Normal.dot Help? Fatally Corrupted; No Fix. Please Assist.	atm0073	Word	2	09-21-2009 04:02 AM