Thread: Calculate Button

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

Code:

Sub Calc()
    Calculate
End Sub

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

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.

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.

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

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

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

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

Two things:

Code:

Worksheets("Sheet2").Calculate

Notice thats plural, worksheets.

Second, the line of code

Code:

ActiveSheet.Paste

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