Simple "Caclulate" not working

[The_Kurgan]

Due to a large number of calulcations and a lot of data, it has been decided to put the Calculate function in the users' hands. Since many are not savvy enough to know to hit F9 or find it in the ribbon, I created a button whose only command is "Calculate" within the code. Yet, for some reason, it only calculates the active sheet.

Private Sub CommandButton1_Click()
Calculate
End Sub

It doesn't get much simpler than this, so... what the heck am I missing???

Thanks in advance!
(Excel 2007)

 

[jim may]

From Help....

Application.CalculateFull Method
Forces a full calculation of the data in all open workbooks.
Syntax

expression.CalculateFull

expression A variable that represents an Application object.

Example


This example compares the version of Microsoft Excel with the version of Excel that the workbook was last calculated in. If the two version numbers are different, a full calculation of the data in all open workbooks is performed.

Visual Basic for Applications
If Application.CalculationVersion <> _
Workbooks(1).CalculationVersion Then
Application.CalculateFull
End If

 

[The_Kurgan]

Thanks for the post, Jim. I tried the Application.CalculateFull and it doesn't like that at all. The calculation takes an inordinate amount of time to run and still does not calculate the other sheet within the worklbook.

There really is no reason this code shouldn't work. When I hit the "Calculate Now" button on the Formulas menu in the ribbon, it works. However, the code that button runs is "Caclulate" as evidenced by recording a macro.

I just noticed, however, that if I create a public sub and call it by my button, it works. Go figure... seems like a glitch to me.

Thanks again!