Disable VBA codes in the file after a certain date

Thank you Mr PaulM100 for your help
I have seen this post before.This is not what I want because It requires to put the code in every sub.
This is what I ask for:
"I need one code to disable all VBA codes( modules,forms and worksheet events) and make the file run without codes."

I don't think you'll be able to do this, each Sub is going to have to check *something* to know if it can run or not.

Or.....in the workbook open event put code to check the date and if > whatever limit you require then save the workbook as a .xlsx and delete the existing .xlsm.....no mode code.

The trouble with that is there is no recovery, once it's removed all the VBA it's gone.

Oops double posted.

I agree with PaulSP8
However rather than putting the code into all subs, you could create a global boolean variable, & have each sub check the variable at the start of the code.

@BellyGas, that would need a saveas, so the original workbook would still be available.

It would, but you could put all the code to complete the operation in a module, export that module at the start of the operation, create a new excel file, import the module with the code to saveas and delete in it....close the existing xlsm and run the code newly imported into the new workbook....which then saveas's and deletes the workbook with the code in it. You'd be left with an xlsx version of the workbook that needs code disabled in and another workbook that doesn't do anything at all except save an xslm as xlsx and deletes the xlsm. Either way, the functional code to be disabled is most definitely disabled.

If the code is vital for the workbook, what is the point of just deleting the code. Why not just delete the file?

Thank you all guys for your help

Originally Posted by Fluff13

I agree with PaulSP8
However rather than putting the code into all subs, you could create a global boolean variable, & have each sub check the variable at the start of the code.

@BellyGas, that would need a saveas, so the original workbook would still be available.

Mr.Fluff13,Could you give me the code to do this?
Note: I don`t want to delete the file.I just want to disable vba codes until I get into the code again and change the expiration date.

At the very top of a regular module (before any code) put

Please Login or Register  to view this content.

In the This workbook module, something like

Please Login or Register  to view this content.

And in every single procedure add this line

Please Login or Register  to view this content.

Very nice Fluff

Originally Posted by Fluff13

At the very top of a regular module

Thank you for your solution but this code doesn't work with UDF
Here is my code:
In workbook open event:

Please Login or Register  to view this content.

in module:

Please Login or Register  to view this content.

In UDF:

Please Login or Register  to view this content.

You have declared expiry in the ThisWorkBook module, it needs to go in a regular module

Do you mean that I should repeat the whole code in thisworkbook to every module?
Show me the right code,please

No, the public expiry as boolean needs to be at the top of any ordinary module, NOT the workbook module. And you'll only need it once.

Originally Posted by leprince2007

Do you mean that I should repeat the whole code in thisworkbook to every module?
Show me the right code,please

In ThisWorkbook

Please Login or Register  to view this content.

At the very top of a regular module (but only one module)

Please Login or Register  to view this content.

in your code/function

Please Login or Register  to view this content.

Thanks for your code Mr.Fluff13 ,it works but the UDF shows "0",I should edit all cells that contain this UDF or its referenced range to make it recalculate.

It won't recalculate if the expiry date is passed, which is what you asked for.

No.I extended the expiration date but the UDF doesn't recalculate.It requires me to re-enter the UDF in cells or edit its range.
I want it to recalculate by itself.
I think this happens because of the UDF functionality: If you edit the code of the UDF ,You should re-enter it again in cells.
I created a simple UDf.see the attachment

This has nothing to do with preventing the code from running.
If you have functions that are not working the way you want, then you need to start a new thread.

It`s a simple UDF (no1*no2).
Your code doesn't work with any UDF.
How to make it work?

If you change the expiry date to 22-Jan then it will prevent the UDF from working & you will get #N/A values, whenever the function runs.

Hi, You must use from "Application.Calculate" in workbook_Open Code,
Sheet automatic update, when open file