Need idea to block all macro in a simple way

[ValerieT]

Hello

I've created a workbook to calculate different HR headcount. These calculations are done by several macros, activated by multiple objects in different sheets.
I want my "super user" to be able to run the macros every month (to build the calculation) but then, when she publish the results to all company, I would like her to be able to "deactivate" all macro easily in one shot?

[patel]

reserve a cell for a flag, all macros check this cell, if value is 1 then exit.
All macros, before end sub change the flag to 1

[HaHoBe]

Hi, ValerieT,

I would use a Name, enter the date of the last execution there and compare the month on opening the workbook to the month stored. If a new month is reached a Boolean could be set to True which is checked by the macros. And I would think about a master macro to start the individual ones and store the new date in the name as well as set the boolean to False.

Instead of a Name you could use a textfile to store the date or a CustomDocumentProperty. I would not recommend to use the Registry to store the information (would only be available on one computer).

Ciao,
Holger

[ValerieT]

Thanks you both, you gave me good ideas to implement

[snb]

If she publishes the calculated workbook as an xlsx file no macros will be present anymore.

[ValerieT]

that's even smarter :-).. sometimes, I forgot the obvious. It will generate error message if they click on the "shapes" but I can live with that...

Once again, thanks snb!

[Paul_Hossler]

I've created a workbook to calculate different HR headcount. These calculations are done by several macros, activated by multiple objects in different sheets.
I want my "super user" to be able to run the macros every month (to build the calculation) but then, when she publish the results to all company, I would like her to be able to "deactivate" all macro easily in one shot?

I usually have my macro-enabled 'master' WB just generate a new WB with just the data I want to share arranged the way I want it, and then save the new one as a xlsx.

Paul