Prevent a macro being run twice in one day!

You will need to set an entry that the macro can accress to see when it was last run.

This setting can be an entry in a cell. There are some cell Properties that are only aviable from VBA code and can be used for this purpose as long as the workbook is saved to retain the value.

You could also use a registry entry. A registry entry would be valid only on the pc that ran the macro.

You could use a text file that could be saved on a local or net drive to hold the last run date.

What ever method you use I suggest that the entry be made at the end of the macro run to allow the macro to be rerun if it crashes on the previous run or if you update the entries when the macro 1st runs you will need to add code to warn the user that the macro had already been started and may have already been run this week whislt allowing the user to cancel or allow the macro to run.

If any of the above suggestions can be implemented I can give you some guidence on some VBA code

Here is 2 examples

Neither example takes into account of multiple sheets & will test via the activesheet.
Sheet references can be added if required.
The workbook must be saved after the date is added to ensure it tests correctly


This example uses A1
The entry is viewable & changable by any user
A1 can not be used for any other purpose
You can change from A1 to anyother cell that suits

Please Login or Register  to view this content.

In this example the macro also uses A2
The entry is only visible and can be changed only by macro code
A2 can still be used in a normal maner like any other cell.
Deleting A2 will cause the macro to beeive the macro has not run this week

Please Login or Register  to view this content.

Another option is to use add another worksheet that can be hidden in so that it can only be accessed by macro code and use 1 of the above examples on this additional sheet

Glad to have been able to help solve your problem

PS which method did you use?