'this workbook will self destruct!'

[beccy]

i am sending a price guide in excel format to customers and I would not like them to reuse or copy the data...

is there a way of setting something that will prevent people copying it and delete the contents of the sheet after a certain period of time?

any thoughts would be appreciated!

 

[beccy]

could i run a macro on start up that would permanently delete the data after a certain amount of time?

 

[gedi]

You could use some like this, change date as required. Put this code in the workbook open event. This will delete the entire workbook.

 If Date < #8/30/2005# Then Exit Sub
        With ThisWorkbook
            .Saved = True
            .ChangeFileAccess xlReadOnly
            Kill .FullName
            .Close False
        End With

 

[Justinlabenne]

Try here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=540

and

http://www.vbaexpress.com/kb/getarticle.php?kb_id=475

 

[jbm222]

The easiest way to keep them from copying the data is to protect the sheet with a password. If you protect a document in Excel 2003, it gives you an option of things people can and cannot do. Simply clear the check boxes saying 'Select Locked Cells' and 'Select Unlocked Cells'.

Then no one will be able to copy the contents of the sheet to their clipboard without cracking the password (which is unfortunately very easy to do).

A really cheap, lazy, and possibly less than sufficient way to make it "self destruct" would be to apply conditional formating to each cell.

Select all the cells you want to 'destroy' and apply a conditional format, with a formula that says something like:

= today() > mm/dd/yy

The date of course being the last date you want the file to be readable.

Then set the format to something that makes the data unreadable (black background or white text... change the font to wingdings... whatever... get creative).

You could use the same idea, combined with an if statement and in each cell, change the current value to:

= if ( today()>mm/dd/yy , " " , Normal Value for cell )


Neither of these methods is very sophisticated and they don't completely destroy the data. It probably wouldn't be very difficult to destroy the data using a macro of some sort, but then you would have to find a way to prevent people from opening the file if they disable the macros.

You might want to look into using a more secure document, such as a pdf if you really don't want them to copy the data, but then it's going to be a lot harder to make it self destruct.

 

[jbm222]

Try here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=540

and

http://www.vbaexpress.com/kb/getarticle.php?kb_id=475

I like that first link.

How can you automatically run that code upon exiting the file?

 

[beccy]

i'm going to use the trial period one...

how can i force the user to enable macros?

 

[Joe4]

Instead of dealing with all of that, have you considered the possibility of just sending them the file in PDF format? Then you don't need to worry about enabling macros and all the other stuff.

 

[beccy]

yes i have thought of that but they upload the price book into their system and use the prices. i just need to make sure it cannot be reused

 

[Purple Youko]

How about having a macro that actually enables the spreadsheet to run at the start. Maybe it will place a few values into cells that were previously empty.
This would mean that if the macros were disabled then the sheet would be pretty much useless.