Create simple commandbutton for manual recalculation

B

Bryan Linton

I've written a spreadsheet that pulls data from an Access database and does
extensive calculations. This lags the spreadsheet so much that it's
difficult to enter data due to unresponsiveness, so I turned off automatic
calculation. However, I believe this disables automatic calculation for
Excel generally. This spreadsheet will be used by our salesforce, and not
all of them are computer-savvy. They also use other spreadsheets, and I
think it's asking too much of some of them to turn auto-calcluation on and
off when they use different spreadsheets. So what I'd like to accomplish is
the following:

- Have *manual* calculation saved as the default behaviour for *this
spreadsheet only*.
- Place a commandbutton (or the most appropriate widget) in the spreadsheet
itself. The user would click this "Calculate Now" button to perform manual
calculation of the spreadsheet once all data have been entered or modified.

It seems simple enough and I thought I could figure it out, but so far I
haven't gotten very far. I've tried searching online and using excel's
built-in help, but to no avail. Any and all help is much appreciated.

Thanks!

Bryan
 
E

Ed

Hi, Bryan. I'm not the most savvy person, so take my comments with large
grains of salt. But here's where I would start:
- Have *manual* calculation saved as the default behaviour for *this
spreadsheet only*.
Click to expand...

In the ThisWorkbook module:
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub
- Place a commandbutton (or the most appropriate widget) in the spreadsheet
itself. The user would click this "Calculate Now" button to perform manual
calculation of the spreadsheet once all data have been entered or
Click to expand...
modified.

In the Sheet module:
Sub Calc
Calculate
End Sub

Assign this to your button.

These specific items are untested, though I've used similar items. Like I
said, I'm not the greatest, but this is where I would start.

HTH
Ed
 
Top