Clear contents based on date

[deemer98]

Good afternoon all. Is there a way to set a formula or conditional format that will clear the contents of a cell after so many days?

Cell D1 contains a date. After365 days, I want that cell to clear the date out. Is that possible?

Thanks!

 

[deemer98]

No, same thing....date is still showing

 

[Joe4]

No, same thing....date is still showing

I have tested it out with a couple hundred values, and it works for me.
To confirm:
- The code must be in the "ThisWorkbook" module
- VBA code MUST be enabled
- There is only one sheet in the workbook
- Dates entered must be more than one year old and entered into odd numbered rows (between 3 and 59) in columns D:AA
- Dates must be entered as valid dates, and not as text

Two things to test:

1. Amend the code to add a MsgBox row to the code, like this:

Private Sub Workbook_Open()

    Dim cell As Range
    Dim cl As Long
    Dim rw As Long

    MsgBox "Code is running", vbOKOnly
    
    Application.ScreenUpdating = False

'   Loop through columns D to AA
    For cl = 4 To 27
'       Loop through odd number rows from to 3 to 59
        For rw = 3 To 59 Step 2
'           Check dates in cells to see they are more than a year old
            If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then
'               Clear date from cell
                Cells(rw, cl).ClearContents
            End If
        Next rw
    Next cl
    
    Application.ScreenUpdating = True

End Sub

When you open your workbook, you should get the message "Code is running". If not, VBA has not been enabled and the code is not running.

2. Identify a cell that should be cleared, but is not.
What is that cell address?
What is the exact entry in that cell?

Let's say that it is cell D7. Enter this formula in any blank cell and tell me what it returns (for whatever cell is not working correctly):

=ISNUMBER(D7)

 

[deemer98]

I have tested it out with a couple hundred values, and it works for me.
To confirm:
- The code must be in the "ThisWorkbook" module
- VBA code MUST be enabled
- There is only one sheet in the workbook
- Dates entered must be more than one year old and entered into odd numbered rows (between 3 and 59) in columns D:AA
- Dates must be entered as valid dates, and not as text

Two things to test:

1. Amend the code to add a MsgBox row to the code, like this:

Private Sub Workbook_Open()

    Dim cell As Range
    Dim cl As Long
    Dim rw As Long

    MsgBox "Code is running", vbOKOnly
   
    Application.ScreenUpdating = False

'   Loop through columns D to AA
    For cl = 4 To 27
'       Loop through odd number rows from to 3 to 59
        For rw = 3 To 59 Step 2
'           Check dates in cells to see they are more than a year old
            If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then
'               Clear date from cell
                Cells(rw, cl).ClearContents
            End If
        Next rw
    Next cl
   
    Application.ScreenUpdating = True

End Sub

When you open your workbook, you should get the message "Code is running". If not, VBA has not been enabled and the code is not running.

2. Identify a cell that should be cleared, but is not.
What is that cell address?
What is the exact entry in that cell?

Let's say that it is cell D7. Enter this formula in any blank cell and tell me what it returns (for whatever cell is not working correctly):

=ISNUMBER(D7)

Maybe I am missing how to ENABLE macro? This macro stuff is all new to me.

After I pasted your updated code into WORKBOOK, I then saved it and it said I had to save as a macro enabled excel. I saved it. When I reopened the workbook, I did not see "code is running"

 

[Joe4]

When you open the Workbook, you see a warning just below the menus regarding opening the workbook with macros/vba?

See here for more details on enabling macros: Enable or disable macros in Office files

 

[deemer98]

Yes, that is what I see. I click on Enable, but the dates still don't disappear

 

[Joe4]

Yes, that is what I see. I click on Enable, but the dates still don't disappear

Do you get the Message Box pop-up?

Please address all the things I listed in post 12, providing all the information that I asked for.

 

[deemer98]

My answers are at the end of your questions....I really appreciate all your time and help with this!


When you open your workbook, you should get the message "Code is running". If not, VBA has not been enabled and the code is not running. (I opened the workbook and there is no "Code is running" message)(I do get the SECURITY WARNING Macros have been disabled. Enable Content). I click on Enable Content

2. Identify a cell that should be cleared, but is not.
What is that cell address? W7
What is the exact entry in that cell? 28-Sep-19 (formatted as a date by right clicking and selecting date

Let's say that it is cell D7. Enter this formula in any blank cell and tell me what it returns (for whatever cell is not working correctly): TRUE

 

[Joe4]

If Macros are enabled, and you are not getting the "Code is running" message, it can really only mean one of two things:
1. You did not place the code in the correct module (needs to be in the "ThisWorkbook" module of this particular file.
- or -
2. You changed something about the code, so it is not being recognized as Event procedure code, and won't run automatically. Did you copy and paste the code, exactly as I have written it?

 

[deemer98]

If Macros are enabled, and you are not getting the "Code is running" message, it can really only mean one of two things:
1. You did not place the code in the correct module (needs to be in the "ThisWorkbook" module of this particular file.
- or -
2. You changed something about the code, so it is not being recognized as Event procedure code, and won't run automatically. Did you copy and paste the code, exactly as I have written it?

Yes Sir, I am putting it in the ThisWorkbook module. My folder path looks like this: VBAPROJECT (Attendance - New Version4.xlsm)
Microsoft Excel Object folder
Sheet1 (Attendance) (I'm not pasting code in here)
ThisWorkbook (I am pasting code in here)

I copied and pasted the code from your #12 post and this is what I copy/pasted:

Private Sub Workbook_Open()

Dim cell As Range
Dim cl As Long
Dim rw As Long

MsgBox "Code is running", vbOKOnly

Application.ScreenUpdating = False

' Loop through columns D to AA
For cl = 4 To 27
' Loop through odd number rows from to 3 to 59
For rw = 3 To 59 Step 2
' Check dates in cells to see they are more than a year old
If (Cells(rw, cl) > 0) And (Date > (Cells(rw, cl) + 365)) Then
' Clear date from cell
Cells(rw, cl).ClearContents
End If
Next rw
Next cl

Application.ScreenUpdating = True

End Sub

 

[Joe4]

I am stumped, unless your Macros/VBA still aren't being enabled.

Can you try inserting a new VB Module in your workbook, and then add this code:

Sub Test()
    MsgBox "This is a test", vbOKOnly
End Sub

Save your workbook and exit.

Then open your workbook again, and enable macros/vba.
Now, from the Excel worksheet go to View -> Macros and select the "Test" macro and click run.
Does that return the "This is a test" message?