AUTO/MANUAL Calculation mode marker ?

Hey everyone,

Long story short, I'm currently working on a project that necessitates constant switch between AUTO and MANUAL calculation mode (used to speed up data collecting on the web). And it works great for the most part, except when we have to stop a procedure midway through, since it only reverts back to AUTO calculation at the very end of the code. Personally, I'm fine with it, I just switch it back to AUTO by pressing a button on our sheets...but that's the kind of thing my partner easily forget.

So I'm trying to add a marker that would show what the current calculation mode is set on to all our pages' headers (which relatively look all the same). When Calculation mode is automatic, have "AUTO" displayed in cells on various sheets. When Calculation mode is manual, have "MANUAL" displayed in the same cells.

I'm just not entirely sure how to write it. For one, I've never wrote any VBA that's not activated by a button. I'm guessing somekind of OnEvent in the ThisWorkbook tab...but I took a look at the list in the second box and couldn't see anything that would initiate the procedure on an Application.Calculation change. Or maybe I'm just not thinking about it the right way at all.

Any gentle soul have something to get me started ?
The somewhat VBA-newbie in me would be very grateful.

Originally Posted by AlphaFrog

Whenever you change the calculation mode in your code, include a line that displays the mode in a cell e.g.

Application.Calculation = xlCalculationManual
Range("A1").Value = "Manual"

Application.Calculation = xlCalculationAutomatic
Range("A1").Value = "Automatic"

Yeah, I thought about that.

I do have an hidden sheet where I write/store many different things like that using VBA (like last refresh time for example). I then use a simple = formula in the cells I want the info to be displayed in, that refers to that unique cell on my hidden sheet. But I was hesitant to go that route, as it seemed a bit sketchy to rely on formulas for an AUTO/MANUAL calculation marker, and wanted to avoid using the VBA to Range("").Value to write on 20 different sheets or so. Plus, thought I could maybe learn a new trick.

So do I have to assume that this can't be done via an OnEvent procedure ? If so, I'll probably go with what you suggested.

Applied your suggestion...seems to work like a charm.

I've set it to write "Manual" on my Hidden page before the Application.Calculation = xlManual. That way, all the formulas on my other page have the chance to adjust before it switches to manual.

Sheets("HIDDEN").Range("F6").Value = "MANUAL"
  Application.Calculation = xlManual

Then, set it the other way around for automatic.

Application.Calculation = xlAutomatic
  Sheets("HIDDEN").Range("F6").Value = "AUTOMATIC"

Looks like all the pages are reacting well to the change, which was what worried me.

Lockin' this now.
Thanks for your input, consider yourself rep'd.