For a setup like that you need two macros. The first one is a "Worksheet_Change Event" macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
Call Solv_Run
End If
End Sub
This will fire whenever a value is entered in either cell A1 or A2. Copy this macro, right click on the sheet tab where you have your solver model and click "View Code". Paste the "Private Sub ..." macro in the windows that opens.
The second macro you need is the solver macro.
Sub Solv_Run()
SolverReset
SolverAdd CellRef:="$W$13", Relation:=2, FormulaText:="$H$7"
SolverOk SetCell:="$W$16", MaxMinVal:=2, ValueOf:="0", ByChange:="$W$7"
SolverSolve Userfinish:=True
End Sub
This macro must be pasted to a module in Visual Basic. Go to "Developer" tab, click "Visual Basic" click "Insert" and select "Module" and paste the "Sub Sol_Run" code in the new windows that opens.
Before you can run solver by using a macro you must set a reference to Solver in Visual Basic. In the Visual Basic window click "Tools" then select "References". Look for Solver and tick box in front of it to set the reference. If solver not found use browse and get Solver.xlam
Usually found "\Microsoft Office\Office 14\Library\Solver\Solver.xlam"
Alf
Bookmarks