CODEWITHSUNDEEP
excelvba
Bobsworth
Bobsworth

Reputation: 43

Update charts during ActiveSheet.Calculate

Some Background: I have a table of counts which is graphed. When slicers are changed I need to click Calculate to update the counts and graph. (Calculation is set to manual due to workbook size). Believe it or not, using the calculate button is beyond some users.. I would like to have it as a form button easily visible next to the graph. I used the Macro recorder to find the statement for Calculate Sheet. It gave me ActiveSheet.Calculate.

Problem: When I run ActiveSheet.Calculate from my macro button the graphs on the sheet do not update. Excel's calculate button, however, works. Surely they are functionally identical? Why does my button not update but Excel's calculate does?

When I change the button to use Application.Calcluate, the graphs update.

Upvotes: 2

Views: 8155

Answers (1)

GlennFromIowa
GlennFromIowa

Reputation: 1646

Well, on my version of Excel (2010), there are two Calculate buttons: Calculate Now F9 and Calculate Sheet Shift-F9. Calculate Now re-calculates the whole Workbook and should be functionally identical to Application.Calculate, whereas Calculate Sheet only recalculates the individual worksheet and should be functionally identical to ActiveSheet.Calculate.

I would suggest using the Workbook.RefreshAll method or the WorkbookConnection.Refresh method before the ActiveSheet.Calculate to see if that has less overhead than the Application.Calculate method.

Oh, and I can definitely believe using the calculate button is beyond some users.

Upvotes: 1

Related Questions