Macro to Protect file

[Zach]

I have created a file for my boss that has some worksheet protections in it. She has the password to unlock it and adjust the information, but I'm looking to create a macro to reprotect the worksheet anytime she closes it and forgets to reprotect it.

Is that possible?

 

[SirJB7]

Hi, Zach!
Yes, it's possible trapping the workbook events BeforeClose and/or BeforeSave, or the Open event too. It'll all depends on how the workbook is opened, modified, and saved or not (and what should be done), on how it'd be opened, and so.
Any chance elaborating a bit more?
BTW, checked this yet?
http://chandoo.org/forum/threads/excel-2007-security-best-practices.6348/#post-35991
http://chandoo.org/forum/threads/user-form-via-qat.5203
http://chandoo.org/forum/threads/user-form-via-qat.5203/#post-28086
Regards!

 

[Zach]

Ok so I've attached the file in question. currently the sheet protection is on, which allows for all the date cells that aren't yellow to be altered. However if you enter the password and unlock the cells you can change the yellow cells as well. There are occasions that my boss alters the information, saves the file and closes it and forgets to reprotect the file and I'm trying to find a way to have it automatically protect itself before fully closing.

 

[Smallman]

Hi Zach

You need to work out the level of macro protection you want. Having said that someone that knows what they are doing will not be stopped by any level of macro protection in an XL workbook. You will need to change the password and in your book as there is already y macro protection on the file I have left the coding commented out.

Hope this gets you started.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet4.Protect "Password"
    Sheet5.Protect "Password"
End Sub

Take care

Smallman

 

[Abhijeet R. Joshi]

I agree with Smallman on this, it is the very correct way to have the sheets protected since Workbook_BeforeClose triggers the closing of the workbook and fires the macro..