Making the Tools>Options...Calculation "Manua"l Vs "Automatic" a Button.

I've got a spreadsheet with a lot of calculations embedded in it.

When I'm doing core work I go into Tools>Options and on the Calculation tab set it to "Manual" so I don't have to worry about waiting for a re-calculation every time I make a change. With this setting in manual I can re-calculate any time by pressing "F9".

Here's the question: if I were to leave the calculation option on "Manual" permanently, in the case of another user who was working with my sheets not knowing about the "F9" functionality; is there any simple way to make a "re-calculate" button that would do this?

FYI, I am not versed in macro's and/or vbasic at all.

Thx for your help.

Hello Ecoich,

Yes, you can. First copy the macro code below. Instructions follow on how to place the macro into a module. Next you need to add a command button to the Worksheet.

Adding Buttons to the Worksheet

1. Click View on the Excel Menu Bar or use ALT+V.
2. Click Toolbars or type T to display the available toolbars.
3. Click on Forms or use the Down Arrow to select this toolbar.
4. Look for the rectangular icon. When you place the mouse on it you will see Button displayed.
5. Click on this icon.
6. Move the cursor to the cell where you want the button. Left Click and Hold the button down while you move the mouse. This will draw the button. Release the button when you are done.
7. A dialog box will appear asking you to assign a macro. Close the dialog. The macro will be added later.
8. Repeat steps from 4 on to add more buttons.

Macro Code

Please Login or Register  to view this content.

Adding the Macro to the Workbook
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

Assign the Macro to a Button
1. Right Click on the Button you wish to assign a macro to.
2. From the pop up menu, Click on Assign Macro...
3. A dialog box will appear with a list of macros. Select Calculate from the list, and Click OK.
4. Press CTRL+S to save the changes

Your finished!

Sincerely,
Leith Ross

Leith,

Thanks a lot for the step-by-step...worked like a charm.

Cheers!
E