Make content deletion on timer

Modify code below and place in ThisWorkbook module, for required workbooks.

'This example clears cells after 10 hrs

Dim clearTime

clearTime = Now + TimeValue("10:00:00")
Application.OnTime clearTime, "clear_cells"

End Sub

Sub clear_cells()
Range(Cells(1, 1), Cells(2, 2)).Clear
End Sub

The problem with times for long intervals is that the excel sheet needs to be open for the complete duration of the timer period.

It might be advisable to instead delete the data whenever the worksheet is closed or even better to clear all unnecessary information on opening the file.

The reason I did not want to delete on exit is, a supervisor may need to close the workbook a couple of times during his/her shift, but come back and still need the data in the status column. This workbook is used to filter and sort who will be forced to work an overtime shift for the next shift because of a sick call. If the sick call comes early in the shift the supervisor will open and start calling.Sometimes he/she will be called away and need to close the workbook. Also some days even after completing the ratchet additional sick calls will come in, so that is the reason I don't want the column clear until after the up coming shift is filled and has started.

If you need to clear the cells at a specific time after the
shift starts regardless of when the workbook is opened,
then the code can be modified as follows:

Sub Timed_Clearing()

Dim endTime
Dim clearTime
'Set end time when cells should clear
'Assume end times to clear - 07:30 AM / 03:30 PM / 11:30 PM
If Hour(Now) >= 0 Then endTime = TimeValue("07:30:00 PM")
If Hour(Now) >= 8 Then endTime = TimeValue("13:30:00 PM")
If Hour(Now) >= 16 Then endTime = TimeValue("23:30:00 PM")

clearTime = endTime
Application.OnTime clearTime, "clear_cells"

End Sub

Sub clear_cells()
Range(Cells(1, 1), Cells(2, 2)).Clear
End Sub

Note: The above module can be placed and triggered in a worksheet
module or any other module as you require, and is not dependent on
time when workbook is opened, but you must set the time specific time
close to end of shift, when cells are to be cleared.

the workbook is opened

I modified the code above, but it is not doing anything. Not sure what I have done wrong?

Sub Timed_Clearing()

Dim endTime
Dim clearTime
'Set end time when cells should clear
'Assume end times to clear - 10:00 AM / 06:00 PM / 02:00 AM
If Hour(Now) >= 16 Then
   endTime = TimeValue("20:30:00")

   clearTime = endTime
   Application.OnTime clearTime, "clear_cells"

End Sub


Sub clear_cells()

Range("G4:G87").Clear
End Sub

Select allOpen in new window

I placed the following code in the "Open Event" in ThisWorkbook, but I get a error code Mismatched type

Worksheet(3)=Graveyard
Worksheet(4)=Dayshift
Worksheet(5)=Swingshift

Private Sub Workbook_Open()

Dim EndTime
Dim ClearTime
Dim Worksheet


' Clear all contents in Column "G" upon opening
'Set end time when cells should clear
'Assume end times to clear - 10:00 AM / 06:00 PM / 02:00 AM

If Hour(Now) >= 8 Then Worksheet(3).EndTime = TimeValue("10:00:00")
If Hour(Now) >= 16 Then Worksheet(4).EndTime = TimeValue("18:00:00")
If Hour(Now) >= 0 Then Worksheet(5).EndTime = TimeValue("02:00:00")

ClearTime = EndTime

Application.OnTime ClearTime, "Clear_Cells"

End Sub

Sub Clear_Cells()
Range("G").Clear

End Sub

Select allOpen in new window

Below is the configuration for clear times 02:00 AM, 10:00 AM and 06:00 PM

Note: You need to keep the complete If-Then statement in one line to avoid End-If requirement.

Also, if you start the timer after the required time passed, you might need to close the workbook
and then re-open it before you try again else nothing will happen until the previous time is reached.

Sub Timed_Clearing()

Dim endTime
Dim clearTime
'Set end time when cells should clear
'Assume end times to clear - 02:00 AM / 10:00 AM / 06:00 PM

'Clear at 2:00 AM
If Hour(Now) >= 18 Or Hour(Now) < 2 Then endTime = TimeValue("02:00:00 AM")
'Clear at 10:00 AM
If Hour(Now) >= 2 And Hour(Now) < 10 Then endTime = TimeValue("10:00:00 AM")
'Clear at 06:00 PM
If Hour(Now) >= 10 And Hour(Now) < 18 Then endTime = TimeValue("18:00:00 PM")

   clearTime = endTime
   Application.OnTime clearTime, "clear_cells"
End Sub


Sub clear_cells()

Range("G4:G87").Clear
End Sub

Should this code be associated with a command button or will it run in the Open Event? Not 100% sure where I should place it. My wish is that it runs when the workbook is opened.
Thanks for your patience

This is what the workbook looks like with all three sheets. Ratcheting-List--Post-Copy-r1.xls

Modified Workbook attached.

Notes:
(1) Yes, Workbook_Open() module is used to start timer
(2) New Module "Module1" added for Clear_Cells() module / subroutine
(3) Clear_Cells() module / subroutine is DELETED from Workbook_Open() module

See below:

 Workbook_Open() module:
--------------------------------
Private Sub Workbook_Open()

Dim EndTime
Dim ClearTime
Dim Worksheet


' Clear all contents in Column "G" upon opening
'Set end time when cells should clear
'Assume end times to clear - 02:00 AM / 10:00 AM / 06:00 PM

'Clear at 2:00 AM
If Hour(Now) >= 18 Or Hour(Now) < 2 Then EndTime = TimeValue("02:00:00 AM")
'Clear at 10:00 AM
If Hour(Now) >= 2 And Hour(Now) < 10 Then EndTime = TimeValue("10:00:00 AM")
'Clear at 06:00 PM
If Hour(Now) >= 10 And Hour(Now) < 18 Then EndTime = TimeValue("18:00:00 PM")

ClearTime = EndTime

Application.OnTime ClearTime, "Clear_Cells"

End Sub


Module1:
----------
Sub Clear_Cells()
For Each x_sheet In Sheets
  'Clear the ranges for only GRAVEYARD / DAYSHIFT / SWINGSHIFT sheets
  If UCase(x_sheet.Name) = UCase("GRAVEYARD") _
   Or UCase(x_sheet.Name) = UCase("DAYSHIFT") _
   Or UCase(x_sheet.Name) = UCase("SWINGSHIFT") Then
     x_sheet.Range("g4:g87").ClearContents
  End If
Next

End Sub


Regards


UserForm-Time-Sample.xls

I have spent the last three day trying to get this to work with no luck. The one thing I think may be missing is there does not appear to anything telling Workbook_Open() which UCase is running in Sub Clear_Cells()
If Hour(Now) >= 18 Or Hour(Now) < 2 Then EndTime = TimeValue("02:00:00 AM") Then Run UCase("Dayshift")

Ok I am leaving work at mid-night,  I will take it home with me and open as described above. I did download the UserForm-Time-Sample.xls that you included.
Thank you

I did not have any luck getting to timer to work, but I will keep trying to figure it out. I added a msgbox asking the user if they want to clear the Status Column on all three sheets and I add a button on each sheet so they can clear an individual sheet. Thank you for all the help and patience. UserForm-Time-Sample.xls

Experts1 helped me figure out a way to get this project functional, but most importantly he/she stuck with me through the process.
Many thanks