Make content deletion on timer

I recently asked for help on two different problems on a workbook I inherited from a past employee. Now that the original problems are fixed and the workbook is working as intended, I have discovered a new problem. (I think/hope the last)
Column G "Status" is used as the process is worked each session. A session typically consist of a shift. I don't want the data in that column to be there when the workbook is reopened by a new supervisor the next day or the next week (This process is not always done every day). The data is only valuable during a particular session. I would like to know if it is possible to delete the data based on a timer. (The example that I included only has one sheet "Test Shift", but in fact there is a sheet for each shift "Days", "Swings" & "Graves") I would like to have the Graveyard shift clear the data after 02:00hrs, Dayshift clear after 10:00hrs and Swingshift Clear after 18:00hrs. I am asuming the file has to be open for it to perform this action. Maybe it can be set to run when the file is opened.
I would appreciate any help or direction on this.  Ratcheting-List--Post-Copy-r1.xls
poucedeleonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

experts1Commented:
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

0
Arno KosterCommented:
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.
0
poucedeleonAuthor Commented:
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.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

experts1Commented:
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
0
poucedeleonAuthor Commented:
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

Open in new window

0
poucedeleonAuthor Commented:
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

Open in new window

0
experts1Commented:
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
0
poucedeleonAuthor Commented:
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
0
poucedeleonAuthor Commented:
This is what the workbook looks like with all three sheets. Ratcheting-List--Post-Copy-r1.xls
0
experts1Commented:
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
0
poucedeleonAuthor Commented:
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")
0
experts1Commented:
Note:

Using and testing the "Application.OnTime" function can be very tricky,
because once the Workbook_Open function runs after the workbook is opened,
You will not be able to test it again in the macro, unless you close the workbook
and reopen it again, or to run another macro to cancel the "Application.OnTime"
function setting as below:

Application.OnTime clearTime, "clear_cells", , False

Did you test the "UserForm-Time-Sample.xls" workbook which I uploaded 10/4/11?

Iif you opened the above workbook "UserForm-Time-Sample.xls" at 1:55 AM,
and entered data in column "G" in any of the three worksheets, then at
exactly 2:00 AM, the contents of column "G" of all three worksheets will be deleted.

Whenever you modify the Workbook_Open macro, you will need to SAVE the
workbook, then close it and reopen it again to test the modification you made.

Sorry, but just the nagging requirements of Excel VBA "Application.OnTime"
built-in function, when used with Workbook_Open function!

Regards

 




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
poucedeleonAuthor Commented:
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
0
poucedeleonAuthor Commented:
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
0
poucedeleonAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.