Delete formulas when unprotect

[nbqleebarnes]

Hi All,
I've been busy with a project to sort of protect my excell program, to the general user anyway. I have coded that when the sheet has been unptotected by a user that should not be unprotecting, then I delete the formulas in the cells, I was wondering if there is a way to immediately close the workbook on unprotect, instead of them having to change a cell first after unprotecting. In other words as soon as the user unprotects the sheet I would like to close the workbook immediately.
I have coded so far:-

Sub SheetIsProtected(oSheet As Worksheet)
With oSheet.UsedRange
   If oSheet.ProtectContents = False _
   Or oSheet.ProtectDrawingObjects = False _
   Or oSheet.ProtectScenarios = False Then
      UserForm3.Show
      .Value = .Value
      ActiveWorkbook.Save
      Application.Quit
   End If
End With
End Sub

Thanks in advance
Lee

[CaptRon]

I don't know of a way to instantly close the workbook without some sort of user action to trigger sheet protection detection. However, it does the user little good to remove protection if they can't alter your work. If you are just trying to keep your formulas hidden, Worksheet_SelectionChange will do the trick. They have to select the cell to peek at the formula.

When faced with a similar challenge, I try to be only as aggressive as necessary to keep the overly curious out of the works.

If sheet protection has been removed then I apply one of these approaches:
1. Close the workbook w/o saving changes
2. Delete all sheets (except one), save and close
3. Delete sheets and code

So far, I've found that alerting the user that sheet protection has been removed then closing down the workbook without saving changes has pretty much taken care of the problem.

I've attached a sample workbook illustrating the close w/o saving method.

Ron

[CaptRon]

Lee,

Attached is a sample workbook showing the self-destruct (delete all but one sheet) approach.

It allows the user to print the sheets before deletion. Test on a copy of the workbook or you'll say goodbye to the original.

Ron

[p45cal]

"Nothin's ever sliced so thin, there's not two sides to it."

except, perhaps, a mobius strip?

[CaptRon]

except, perhaps, a mobius strip?

Maybe so. Can you slice one? If so, how many sides would it have? If it can't be sliced, does it qualify?

Ron

[nbqleebarnes]

Thanks CaptRon,
The advise you gave is very useful, and I think that the delete sheets and then save is the way to go.
Cheers
Lee

[mikerickson]

A curious user will find out the formulas if they want to. The easiest way to see your formulas will not be affected by that code.

Sheet Protection, with a password, should be sufficient for the curious, but inexperienced.
It seems to me, that deleting a sheet (i.e. ruining the spreadsheet) is more likely to harm the authorized user for clumbsyness than to add security.

[CaptRon]

Sheet Protection, with a password, should be sufficient for the curious, but inexperienced.
It seems to me, that deleting a sheet (i.e. ruining the spreadsheet) is more likely to harm the authorized user for clumbsyness than to add security.

I confess, I have never known of a user inadvertently removing the sheet protection, given a password was applied. Circumventing the password protection must be done purposely and with some determination. For ordinary users content to use the workbook as designed, password sheet protection is more than adequate and this code presents no hazard.

The approach detailed above is not intended for the ordinary user. Since employing these tactics more than 5 years ago, I no longer have to endure emails and phone calls from users distressed by malfunctioning workbooks "improved" and distributed by some genius too lazy or inept to create his own work but quite sure he knows how to better mine. When just once the workbook disappears before their eyes, word quickly gets around....DON'T REMOVE THE SHEET PROTECTION or the workbook is toast. I do allow them to print each page before destruction (I'm not heartless).

Another part of my need to preserve the workbook intact is that our employees do not have permission or privilege to alter agency forms/reports or workbooks that produce them, though a few are bold enough to try anyway.

All my workbooks open with a notice to the user that sheet protection must remain intact or the workbook will either self-destruct or otherwise become dysfunctional. Rather than destroy the worksheets, forcing the workbook to close without saving has proven equally effective, if properly done. Not as dramatic, but still effective.

Ron