'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!

 

[jbm222]

I made that suggestion in the last line of my first post

*EDIT* to use a PDF that is.

 

[Justinlabenne]

Force them to enable macros huh? Can be done, and there are multiple ways but my buddy johnske's code works pretty good:

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

 

[Legacy 1363]

But then all of the cells would have to be made "uncopiable" or preferably "unselectable" to prevent copying to another workbook and thus unlimited usage.

 

[Justinlabenne]

This can be done, you can lock things down fairly tight and keep most basic users form getting at your data.

Experienced users will have no trouble.

There is a lot involved if you want to prevent copying sheet out, disabling copying of cells, etc....

 

[Legacy 1363]

There is a lot involved if you want to prevent copying sheet out, disabling copying of cells, etc....

It's not so involved, but then it's very easy to circumvent it - and to circumvent everything else that's been suggested.

 

[Justinlabenne]

It's not involved?

It can be quite involved if you want to try and actually accomplish such a thing a a "secure excel environment".

I was refering to the op who asked the question, and they struck me as a newer user who may not want to go to the route of taking over a user's environment just to protect their workbook when other options are more suitable.

I suppose once the work is done, it becomes less involved since you can just reuse the codes.

 

[Legacy 1363]

I suppose once the work is done, it becomes less involved since you can just reuse the codes.

Right. But the main point is that all of these things are easy to circumvent - including your suggestion.

 

[Justinlabenne]

Which one?

 

[Legacy 1363]

I thought you'd only suggested one.
Anyway, I meant about making sure macros are enabled.
All the user has to do after opening the workbook is to copy the data to another workbook.
Or if the macros are not protected, delete them.
Or set EnableEvents to False so that the BeforeClose procedure doesn't work.
Or run code to unhide the sheets.

 

[cfree36]

I got this a long time ago from someone else... I don't remember where so I can't give the credit where it is due....
Anyway the following proceedure will force the user to enable macros.

You have to have one sheet named WARNING and on that sheet have a text box or something that tells the user that they must enable macros. I would also suggest a brief explaination on how to set macro security just in case any of your users have their security level set too high and macros are automatically being disabled.

Then, place the following five Sub Proceedures into the Worksheet module of your workbook.

The last proceedure makes it so the Save As option is not allowed so be sure you have the worksheet named and in the place where you want it to go before adding it.
Now your user can not save the file either.

That proceedure came from Dave Hawley who by the way has some great books on excel!

I would suggest that you password protect your VBA code so the user can not disable this process after opening the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub

Private Sub Workbook_Open()
UnhideSheets
End Sub

Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Warning").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.Name <> "Warning" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True

ThisWorkbook.Save

End Sub

Private Sub UnhideSheets()
Dim sht As Object

Application.ScreenUpdating = False

For Each sht In ThisWorkbook.Sheets

sht.Visible = xlSheetVisible

Next sht

ThisWorkbook.Sheets("Warning").Visible = xlSheetVeryHidden

Application.ScreenUpdating = True

End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim IReply As Long
If SaveAsUI = True Then
IReply = MsgBox("Sorry, this file can not be saved as another name. Press OK to save or, Cancel to Exit.", _
vbQuestion + vbOKCancel)
Cancel = (IReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub