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

Question

This question is a replacement for this question:

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!

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 Peltier, Commented May 3, 2018 at 20:06
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
– PetGriffin, Commented May 5, 2018 at 9:10
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
– Jon Peltier, Commented May 6, 2018 at 14:08

PetGriffin · Accepted Answer · 2018-05-05 09:14:39Z

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!

answered May 5, 2018 at 9:14

PetGriffin

5451 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related