Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans,

    The worksheets are not password protected.

    Where can I put the messagebox code?

    Thank you,
    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect. Thank you Hans.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •