Auto-Delete data on spreadsheet

Topic

New Reply

I would like to delete all data on a spreadsheet after a certain date.

Pseudo code (On Workbook Open):

If date > (hard coded date) then delete all data on worksheet
And message box (OK only) pop up – “Contact your Website admin etc”

I appreciate your help (as always !)

Thank you,
Michael

Reply | Quote

Replies

Please keep in mind that the code won’t run if the user doesn’t enable macros!

Press Alt+F11 to activate the Visual Basic Editor.
Double-click the ThisWorkbook item of your workbook in the Project Explorer (the treeview on the left hand side).
Enter or copy the following code, and adjust the date (US format m/d/yyyy is obligatory):

Code:

Private Sub Workbook_Open()
  Dim wsh As Worksheet
  If Date > #5/1/2009# Then
	For Each wsh In Me.Worksheets
	  If wsh.ProtectContents Then
		wsh.Unprotect
	  End If
	  wsh.Cells.ClearContents
	Next wsh
  End If
End Sub

If the workbook contains worksheets that are protected with a password, the code will fail.

Reply | Quote

Thank you Hans,

The worksheets are not password protected.

Where can I put the messagebox code?

Thank you,
Michael

Reply | Quote

You can put the message box anywhere between the If .. Then and End If lines:

Code:

Private Sub Workbook_Open()
  Dim wsh As Worksheet
  If Date > #5/1/2009# Then
	For Each wsh In Me.Worksheets
	  If wsh.ProtectContents Then
		wsh.Unprotect
	  End If
	  wsh.Cells.ClearContents
	Next wsh
	MsgBox "This workbook has expired. Please contact your website admin.", vbInformation
  End If
End Sub

Reply | Quote

Perfect. Thank you Hans.

Michael

Reply | Quote