VBA: auto-wipe all data when date is reached

[Herpe_derp]

Is there some VBA code that can password protect a workbook when a certain date is reached so the data cannot be viewed or edited by anyone without the password?

 

[Joe4]

Your description of your question does not seem to match your title (your title talks about auto-wiping data, where your question just talks about password protecting it - those are two totally different things, and if you "wipe" the data, password protection really isn't necessary as there will be nothing to see!) So I think we may need some clarification there.

Regarding password protecting the file, it can be done, but I would not count on it.
Excel security is notoriously weak and is VERY easy to break without too much effort by anyone willing to do a little research on the internet.

At best, it acts as a deterement and protects against the "accidental" attempts to see what a user shouldn't.
But it will not prevent anyone motivated to hack it.

So if security is an important piece of this project, Excel is not your best option.

 

[Herpe_derp]

Your description of your question does not seem to match your title (your title talks about auto-wiping data, where your question just talks about password protecting it - those are two totally different things, and if you "wipe" the data, password protection really isn't necessary as there will be nothing to see!) So I think we may need some clarification there.

Regarding password protecting the file, it can be done, but I would not count on it.
Excel security is notoriously weak and is VERY easy to break without too much effort by anyone willing to do a little research on the internet.

At best, it acts as a deterement and protects against the "accidental" attempts to see what a user shouldn't.
But it will not prevent anyone motivated to hack it.

So if security is an important piece of this project, Excel is not your best option.

Hi, you’re right. Changed my mind but forgot to edit the title. Apologies.

The password protect option is the best in my situation as it’s only needed to protect from basic users.

Kind Regards

 

[Herpe_derp]

Your description of your question does not seem to match your title (your title talks about auto-wiping data, where your question just talks about password protecting it - those are two totally different things, and if you "wipe" the data, password protection really isn't necessary as there will be nothing to see!) So I think we may need some clarification there.

Regarding password protecting the file, it can be done, but I would not count on it.
Excel security is notoriously weak and is VERY easy to break without too much effort by anyone willing to do a little research on the internet.

At best, it acts as a deterement and protects against the "accidental" attempts to see what a user shouldn't.
But it will not prevent anyone motivated to hack it.

So if security is an important piece of this project, Excel is not your best option.

Could someone also provide the wipe option too?

 

[Joe4]

Could someone also provide the wipe option too?

You could do something like this, putting the following code in the "ThisWorkbook" module so it runs automatically when the file is opened:

Private Sub Workbook_Open()

    Dim ws As Worksheet

'   Check to see if specified date is passed
    If Date >= DateSerial(2023, 6, 29) Then
'       If it is, loop through all sheets
        For Each ws In ActiveWorkbook.Worksheets
'           Clear contents of each sheet
            ws.Cells.ClearContents
        Next ws
'       Save workbook so data is permanently gone
        ActiveWorkbook.Save
    End If
        
End Sub

You would just need to change the date inside here:

DateSerial(2023, 6, 29)

to match the date you want to use.

Note: There is a BIG caveat here. If the user does NOT enable VBA/Macros on this workbook, the code will never run. So it is super easy to bypass (whether intentional or accidental).
What I have seen some users do is use a "BeforeClose" event to hide most sheets upon closing. Then add code to the "Open" event above to then unhide the sheets.
So if they do not enable Macros/VBA, all but one sheet will be hiddent when opened.
Once again, not foolproof, and easy to get around by anyone motivated enough. Really just designed to prevent honest users from doing things they shouldn't accidentally be doing.