PDA

View Full Version : [SOLVED] Need idea to block all macro in a simple way



ValerieT
11-14-2013, 03:53 AM
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
11-14-2013, 04:25 AM
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
11-14-2013, 04:36 AM
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
11-14-2013, 05:13 AM
Thanks you both, you gave me good ideas to implement

snb
11-14-2013, 06:26 AM
If she publishes the calculated workbook as an xlsx file no macros will be present anymore.

ValerieT
11-14-2013, 06:40 AM
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
11-15-2013, 01:49 PM
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