Auto-Protect multiple Sheets on closing?
 

Hi All......
I have a workbook with three sheets, Sheet1, Sheet2, and Sheet3.... When a
user opens the book, they find all sheets protected with their individual
passwords, (PW1, PW2, and PW3 respectively). The authorized users know the
passwords and will unprotect one or more sheets to edit them. If someone
would be so kind, I would like a "BeforeClose" macro that would reset the
protection on all sheets using the proper passwords. I've tried recording
one but it don't pick up the passwords.

TIA
Vaya con Dios,
Chuck, CABGx3

Auto-Protect multiple Sheets on closing?
 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With thisWorkbook
.worksheets("sheet1").Protect Password:="ABC1"
.Worksheets("sheet2").Protect Password:="EFGH"
.Worksheets("Sheet3").Protect Password:="Snodgrass"
End With
Application.EnableEvents = False
Thisworkbook.Save
Application.EnableEvents = True
End sub

Would be the basic code.

--
Regards,
Tom Ogilvy


"CLR" wrote:

Hi All......
I have a workbook with three sheets, Sheet1, Sheet2, and Sheet3.... When a
user opens the book, they find all sheets protected with their individual
passwords, (PW1, PW2, and PW3 respectively). The authorized users know the
passwords and will unprotect one or more sheets to edit them. If someone
would be so kind, I would like a "BeforeClose" macro that would reset the
protection on all sheets using the proper passwords. I've tried recording
one but it don't pick up the passwords.

TIA
Vaya con Dios,
Chuck, CABGx3

Auto-Protect multiple Sheets on closing?
 

As usual, your stuff works like a charm Tom.........many thanks!

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With thisWorkbook
.worksheets("sheet1").Protect Password:="ABC1"
.Worksheets("sheet2").Protect Password:="EFGH"
.Worksheets("Sheet3").Protect Password:="Snodgrass"
End With
Application.EnableEvents = False
Thisworkbook.Save
Application.EnableEvents = True
End sub

Would be the basic code.

--
Regards,
Tom Ogilvy


"CLR" wrote:

Hi All......
I have a workbook with three sheets, Sheet1, Sheet2, and Sheet3.... When a
user opens the book, they find all sheets protected with their individual
passwords, (PW1, PW2, and PW3 respectively). The authorized users know the
passwords and will unprotect one or more sheets to edit them. If someone
would be so kind, I would like a "BeforeClose" macro that would reset the
protection on all sheets using the proper passwords. I've tried recording
one but it don't pick up the passwords.

TIA
Vaya con Dios,
Chuck, CABGx3