# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Calculate Button

## martynduerden

Hello I am trying to create a Radio button in a cell that calculates the entire workbook rather than pressing F9.

I am not friendly with macro's yet so would prefer not to use any but I must learn....

Cheers

Martyn

----------


## shg

Create an AutoShape or Forms button, and assign this macro:



```
Please Login or Register  to view this content.
```

----------


## JBeaucaire

I can't fathom how creating and coding a special button is better than just pressing F9.  There is always the moment when we all "need to learn them", as you say, but I don't see how this qualifies.

Anyway. Simplest:

1) Turn on the *Control Toolbox Toolbar*
2) Click on *Command Button* icon
3) Draw your button on your sheet somewhere (it will appear with a name like CommandButton1)
4) Right click on your new button and select _Properties_
5) Find the *Caption* and change _"CommandButton1"_ to _"Calculate"_
6) Set your *Font* choice and *BackColor* choice
7) Set *TakeFocusOnClick* to *False* (this will keep your activecell focus where it is, even when you click the button)
8) Close the *Properties* window
9) Right-click the button and select *VIEW CODE*, a VBA window will appear with header/footer for your command button already there, but no code in between, add the word *Calculate* in between.



```
Please Login or Register  to view this content.
```


10) Press *ALT-Q* to close the VBEditor.  
11) Save your sheet.
12) On the *Control Toolbox* toolbar, click on the *EXIT DESIGN MODE* button

Your button is now active. Click your button to calculate the workbook.

----------


## martynduerden

Hi Thank you very much.

As for the reasoning I am writing a program, and I have created a data entry sheet I wanted the button as not all the program users will know that F9 is calculate and on auto calculate all the result cell's have Divide by 0 errors.

----------


## JBeaucaire

> As for the reasoning I am writing a program, and I have created a data entry sheet I wanted the button as not all the program users will know that F9 is calculate



1) A note on the sheet "Press F9 to calculate" pretty much educates new users.





> ...and on auto calculate all the result cell's have Divide by 0 errors.



2) Any formula that divides by a cell that could be empty should be rewritten.  If the formula =A1/B1 gives an error when B1 is empty, evaluate cell B1 first....like so:

*=IF(B1=0,"",A1/B1)*

That technique can be adapted to solve most equations that result in errors when data is missing. It's a habit worth getting into and is far more useful than turning off calculations and hope you/others remember to update  by pressing F9 or a button.

My two cents.   :Wink:

----------


## martynduerden

Hi,

I do intend to clean up the missing data errors but at the moment I am concentrating on getting the program working which is now seriously complex,

Currently the program occupies 61 sheets to produce the required data.

Cheers for all the help it is very much appreciated!

----------


## shg

> Currently the program occupies 61 sheets to produce the required data.



That seems extraordinarily complex for anything short of designing nuclear weapons.

----------


## martynduerden

> That seems extraordinarily complex for anything short of designing nuclear weapons.




You should try being an electrician for a day!  :Wink:

----------


## JBeaucaire

If that takes care of your need, be sure to EDIT your original post, click on GO ADVANCED and set the *PREFIX* box to *[Solved]*.

----------


## philosman

> Create an AutoShape or Forms button, and assign this macro:
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



Is there a code to do a calculate for just that sheet, ie. Shift + F9  ?

----------


## shua79

> Is there a code to do a calculate for just that sheet, ie. Shift + F9 ?






```
Please Login or Register  to view this content.
```

----------


## philosman

Wonderful, many thanks !!!

----------


## philosman

> ```
> Please Login or Register  to view this content.
> ```



I got the following error:

"Compile error:
Sub or Function not defined"

This is the section of the code:

    ActiveSheet.Paste
    Worksheet("Expenses Input").Calculate
    Application.Run "'BUDGET.xls'!GO_Staff"


EDIT
Would this do the same thing?
    ActiveSheet.Calculate

----------


## JBeaucaire

Two things:



```
Please Login or Register  to view this content.
```


Notice thats plural, worksheets.

Second, the line of code



```
Please Login or Register  to view this content.
```


...implies you may be using *.Select* a bit in your code.  Don't do that.

One thing that has to be done is code you learn how to write using the macro recorder (which I still use most days) needs to be cleaned up afterward to remove all the unnecessary selecting.  Post up your entire macro and we can make some suggestions.

----------

