Calculate Button

martynduerden · #1 07-04-2009, 03:20 PM

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 · #2 07-04-2009, 03:31 PM

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

Code:

Sub Calc()
    Calculate
End Sub

JBeaucaire · #3 07-04-2009, 03:39 PM

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.

Code:

Private Sub CommandButton1_Click()
    Calculate
End Sub

martynduerden · #4 07-04-2009, 03:55 PM

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 · #5 07-04-2009, 08:28 PM

Quote:

Originally Posted by martynduerden

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.

Quote:

...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.

martynduerden · #6 07-04-2009, 08:35 PM

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 · #7 07-04-2009, 08:39 PM

Quote:

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

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

martynduerden · #8 07-04-2009, 08:48 PM

Quote:

Originally Posted by shg

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

You should try being an electrician for a day!

JBeaucaire · #9 07-05-2009, 02:18 PM

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 · #10 08-17-2009, 12:06 AM

Quote:

Originally Posted by shg

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

Code:

Sub Calc()
    Calculate
End Sub

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

shua79 · #11 08-17-2009, 12:38 AM

Quote:

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

Code:

 worksheet("Myworksheet").calculate

philosman · #12 08-17-2009, 02:34 AM

Wonderful, many thanks !!!

philosman · #13 08-17-2009, 07:07 AM

Quote:

Originally Posted by shua79

Code:

 worksheet("Myworksheet").calculate

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 · #14 08-17-2009, 11:58 AM

Two things:

Code:

Worksheets("Sheet2").Calculate

Welcome to the Excel Forum

Forum Statistics