Microsoft Excel - If I enter any Value it should be 0 by starting of next week(Monday)

Asked By Saber on 05-Dec-12 07:18 PM
Hi All,

My question is if I enter any value in particular cell it should become 0 after some days.

Suppose I have entered 44.26 in column B4 let say I have entered this  today it should become 0 automatically by Next Monday(Starting of the week).... Is this possible?

Thank you in advance!!!!!!
Harry Boughen replied to Saber on 05-Dec-12 09:21 PM
Hello Saber,

You could store the date of entry and have a macro that executes when the spreadsheet is opened that checks against the current date and clears the entry if the necessary number of days have elapsed.

John D replied to Saber on 05-Dec-12 09:36 PM
This will delete cell B4 every Mondays that you open your workbook (File).
Private Sub Workbook_Open()
If Application.WorksheetFunction.Weekday(Date) = 2 Then
End If
End Sub
It's a workbook event macro so you need to place it in "This Workbook" module
Saber replied to John D on 06-Dec-12 06:57 PM
Hey John,

Thank you for the response!!!!!! Its really helpful...

Can you please do me a favor if you could attach a file that would be helpful. 

John, Please suggest me another macro because If I want to change the day to Tue,Wed,Thur,Fri instead of Monday it will be helpful.i

and John its giving me below error when ever I save the file after writing the Macro. 

The following features cannot be saved in macro -free workbooks:

.VB project

To save a file with these features, click No, and then choose a macro -enabled file type in the File Type list.

To continue savings as a macro -free workbook, click yes

When I say No then the macro which I have written its not getting save. Please advise which settings I have to change in my excel file. And I'm using the Excel 2010. 

Thank you again and again!!!!Your really doing a awesome job by  helping every one here!!!!!
John D replied to Saber on 06-Dec-12 08:58 PM
Here is a link to a sample file

Instruction is on Sheet1
You must save as XLSM file extension, it's an Excel Macro-Enabled Workbook file.
I'm using an older version of XL, the extension is XLS but should work with newer version.
Saber replied to John D on 07-Dec-12 05:32 AM
Hey John,

Thank you!!!!!!!!!!!!!!!Thank you!!!!!!!!!!!!!!!Your awesome!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Good Job!!!!!!!!!!!!!!!!!!!!!!!!!