?
Solved

Make content deletion on timer

Posted on 2011-10-02
15
Medium Priority
?
252 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:poucedeleon
  • 9
  • 5
15 Comments
 
LVL 9

Expert Comment

by:experts1
ID: 36901827
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36903501
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
 

Author Comment

by:poucedeleon
ID: 36904485
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:experts1
ID: 36905247
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
 

Author Comment

by:poucedeleon
ID: 36907752
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
 

Author Comment

by:poucedeleon
ID: 36907953
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
 
LVL 9

Expert Comment

by:experts1
ID: 36908080
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
 

Author Comment

by:poucedeleon
ID: 36908089
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
 

Author Comment

by:poucedeleon
ID: 36908130
This is what the workbook looks like with all three sheets. Ratcheting-List--Post-Copy-r1.xls
0
 
LVL 9

Expert Comment

by:experts1
ID: 36911069
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
 

Author Comment

by:poucedeleon
ID: 36937112
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
 
LVL 9

Accepted Solution

by:
experts1 earned 1000 total points
ID: 36937666
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
 

Author Comment

by:poucedeleon
ID: 36937926
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
 

Author Comment

by:poucedeleon
ID: 36940682
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
 

Author Closing Comment

by:poucedeleon
ID: 36940687
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question