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

0
votes

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!

1
excelvba
Solver works on the active sheet. If the active sheet is a chart sheet, there are no ranges for SetCell and ByChange. Probably if you activate the worksheet at the start of Do_Fit, then Solver will work from the chart button. FWIW, if it were my project, I'd put the chart onto the same worksheet, and not use a standalone chart sheet. – Jon Peltier
Jon - thanks. I activated the worksheet and then ran SOLVER and now it all works. Previous versions of this SELECT'd the worksheet and I never had any issues but it did not occur to me that this was the issue. BTW the reason I use a separate chart sheet is because I can select the range of points to fit and this makes it easier. – PetGriffin
Surprised you didn't have this issue before, because Solver always has worked only on the active sheet. But I'm also glad it was that easy. – Jon Peltier

1 Answers

1
votes

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!