Hi,
Make a copy of your workbook to test with. Put this code in the ThisWorkbook Module:
Private Sub Workbook_Open()
'
'Checks Date to see if 'Cleanup' needed
'
Dim nextrun As Date
'Gets the Monday from hidden sheet 'DateSheet'
nextrun = Sheets("DateSheet").Range("a1")
'Checks if date has past. If not then do nothing
If Now() < nextrun Then
Exit Sub
End If
'If date is today or later do your cleanup code - ONCE
' ...your "Cleanup" code goes here...
'Sets next run date as following Monday 's date
Sheets("DateSheet").Range("a1") = nextrun + 7
End Sub
And this in a standard module:
Sub CreateHiddenSheet()
'
'Run this only once
'
'Adds a Sheet for "housekeeping?" with the name of 'DateSheet'
Sheets.Add.Name = "DateSheet"
'Gets a Monday date from you to start with
ActiveSheet.Range("a1") = InputBox("Enter a Monday date to start with")
'Hides the sheet from the Format/Unhide menu
Sheets("DateSheet").Visible = xlVeryHidden
End Sub
Sub UnhideDateSheet()
'just in case tou need to see it
Sheets("DateSheet").Visible = True
End Sub
Sub RehideDateSheet()
Sheets("DateSheet").Visible = xlveryhidden
To use it: Run the CreateHiddenSheet macro and give it a Monday's date to start with ie: 6/6/2005 or 13/6/2005. Save and close your workbook. Open it again and you should be in business. The CreateHiddenSheet macro will never be used again....but will give you a hidden sheet in your workbook
If you need to change the hidden sheet then you'll have to run 'UnhideDateSheet', change it and then run 'RehideDateSheet'.
If you want, you could protect the hidden sheet too, as long as you include a line that says "Application. userinterfaceonly=true" so the macro can update the protected sheet...
And here's a bonus!
Sub PostStaticDate()
'
'Bonus Sub: How to set a static date:
'
Range("A1") = DateSerial(Year(Now), Month(Now), Day(Now))
End Sub
Thats probably enough for now!
Cheers,
dr