Turn Protection On Automatically When Saving

[jdu007]

Hi Folks.

I thought this would be a commonly-asked question, but it appears not...!

I'm looking to protect the worksheets of an Excel spreadsheet, and I'd normally just 'Protect Sheet' - but I know that other users of the spreadsheet may forget to re-protect it when closing; or, worse still, protect it with a different password, or wrongly-spelt from the original.

I'd like to protect the worksheets automatically upon saving / closing, without the need to re-type the password each time (using a pre-determined password). But each time the workbook is re-opened I'd like to have the sheet read-only, unless then unprotected using the password.

Does that make sense?!?

Thanks in advance,

John.

 

[Smitty]

This should be a start:

<font face=Calibri><SPAN style="color:#00007F">Const</SPAN> strPassword  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "bigdog"<br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            ws.Protect strPassword<br>        <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[poolhall]

I guess you can use Workbook.BeforeClose or Workbook.BeforeSave events as referenced here:

http://msdn.microsoft.com/en-us/lib...ools.excel.workbook.beforeclose(v=vs.80).aspx

http://msdn.microsoft.com/en-us/lib...ools.excel.workbook.beforeclose(v=vs.80).aspx

 

[jdu007]

Hmmm - Thanks for the swift replies.

Smitty - I like the look of yours, but it doesn't seem to be working(?!). I'm not being prompted for a password at all, and can change and save the sheets at will.

Any clues?

Just to re-iterate - I'd like it protected (password 'bigdog'), and that protection to be automatically switched on upon closing - But the user needs to enter the password to make changes, otherwise it's read-only.

Apologies if I'm doing something wrong.

Many thanks,

John.

 

[poolhall]

jdu007,

it works. Make sure you place the code provided by Smitty into the ThisWorkbook module.

 

[jdu007]

Thanks poolhall - That's what I was doing wrong! I had the code in Sheet 1.

Works a treat, thanks to you both.

John.