excel - EXCEL VBA does not run SOLVER inside a macro invoked from a chart button

Question

This question is a replacement for this question:

EXCEL VBA ignores Solver changes values

because there seems to be another issue not appreciated in the original question.

I have the following snippet of Excel VBA code to solve a minimisation problem:

SolverOk SetCell:="$AP$13", MaxMinVal:=2, ValueOf:=0, ByChange:="$AP$9:$AP$11", Engine:=1
SolverSolve UserFinish:=True

This runs in a macro (Do_Fit) triggered by pressing a button on a worksheet. I have another button on a chart sheet (showing the fit) which also runs Do_fit. SOLVER runs when I press the worksheet button but not when I press the chart button. The worksheet has a Worksheet_change method which contains this statement:

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

to process changes made to a specific part of the worksheet, which is not affected by Do_fit. Not sure if this has anything to do with the problem but thought it worth mentioning. Update: when I disable the Worksheet_Change macro, SOLVER still does not run when pressing the chart button

I tried resetting the problem using SolverReset but this produces an error saying Excel has been exhausted.

I don't understand why SOLVER runs when I press the worksheet button but not the chart button and would appreciate any suggestions on how to solve this

Thanks!

标签： excelvba

Answer 1

Following Jon Peltier's suggestion, adding Sheets("calcs").Activate (calcs is the name of the worksheet) to the VBA code ahead of the SolverOk statement did the trick. Thanks!