VBA: Solver - Sheet must be selected?

Hi

I am fairly new to VBA and have just encountered a problem when dealing with the Solver Add-in.

I'd like to run the below code without having to select the sheet "IRR".
Unfortunately, it only works when "IRR" is selected (otherwise the solver results are posted in Cell B40 on the active sheet). How can I change the code so that it fits my needs?

Please Login or Register  to view this content.

Thanks a lot for your help!

Originally Posted by Alf

The way solver is build it will only run from the active sheet so why not add a line to your macro after the "Dim" statment i.e.

Please Login or Register  to view this content.

Exctract from www.solver.com the makers of solver



Alf

Hi Alf

Thanks for your quick reply.

That's actually what I already did...but then there is another problem.
I want to automatically run the macro before the file (several selected sheets) is printed. Unfortunately, the macro then de-selects all sheets...
Do you know how I'd have to adjust the code to re-select all sheets that have been selected before the solver part of the code?

My current code looks like this:

Please Login or Register  to view this content.

Thanks and regards

Not really sure I understands your problem. How are the sheets selected and what stopps you for selecting them again after the Solver run?

For example a macro like this will activate all sheets in a workbook one after another.

Please Login or Register  to view this content.

Alf

Originally Posted by Alf

Not really sure I understands your problem. How are the sheets selected and what stopps you for selecting them again after the Solver run?

For example a macro like this will activate all sheets in a workbook one after another.

Please Login or Register  to view this content.

Alf

Sorry, if I have not expressed myself clearly.

The workbook contains several worksheets that are used to create a report. Depending on the requirements of the client/project different worksheets are used to create the report.
The aim is that people can select (ctrl+left click) all the required sheets and then print the document or save it as PDF (thus, only the selected sheets are saved/printed). To make sure that all values are correct (and up to date) I want a macro to run before the file is printed or saved. The macro should, inter alia, include the code for the Solver Add-in. However, as I have to activate a specific sheet ("IRR") to run the solver, all the selected sheets (the sheets I want to print or save as PDF) are deactivated and will not be printed or saved as PDF.

I'd need a macro that reactivates/reselects the sheets that were selected before the code to run the solver deactivated them.

Any help is highly appreciated!

The simplest solution in my oppinion would be to first run Solver then let the people select the sheets to print.

If that's not possible I think you may need a list box setup where selected sheets are added to an array then after the solver run the array is used to reselect the sheets.

As arrays are really not my "cup of tea" I'm sorry but I can't help you any further.

Alf

Thanks, shg!
This works great!

And also a big thank you to Alf for all the input.

Originally Posted by shg

Please Login or Register  to view this content.