Macro to run automatically on Open/Close

S

Sarah (OGI)

I've created a macro that changes the auto calc function to
manual(Tools|Options|Calculation|Manual). Here is the code it generates:

Sub AutoCalcOff()
' AutoCalcOff Macro
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

I'd like this macro to run automatically on opening this document - not sure
how to do this.

On close, I'd also like the system to be able to revert back to the previous
setting, i.e. if the auto-calc was previously set to automatic and the macro
changed it to manual, I'd like the setting to be returned to its previous
state.

Any ideas?

Thanks in advance.
 
J

Jim Jackson

The Subs you want are named
Sub Auto_Open() and
Sub Auto_Close()

They may be put in any module.
 
T

Tom Ogilvy

Also look at the newer workbook open and workbook beforeclose events

http://www.cpearson.com/excel/events.htm

You would need to store the current settings in your open/autoopen either
using a public variable or something more persistant such as a defined name
or written to a worksheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top