Greetings. Could someone please point me in the right direction with a bit
of code? I have a workbook with 50+ worksheets, but I only need a certain 2
worksheets protected. I also want this macro to run when I close the
workbook, so that I don't have to run the macro myself (I'd probably forget).
Could someone show me how to accomplish this task? Thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets(Array("Sheet2", "Sheet3"))
ws.Protect Password:="justme"
Next ws

End Sub

Entered in ThisWorkbook module.



Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 16:41:00 -0700, Josh wrote:

Greetings. Could someone please point me in the right direction with a bit
of code? I have a workbook with 50+ worksheets, but I only need a certain 2
worksheets protected. I also want this macro to run when I close the
workbook, so that I don't have to run the macro myself (I'd probably forget).
Could someone show me how to accomplish this task? Thanks

Thanks!

"Gord Dibben" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets(Array("Sheet2", "Sheet3"))
ws.Protect Password:="justme"
Next ws

End Sub

Entered in ThisWorkbook module.



Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 16:41:00 -0700, Josh wrote:

Greetings. Could someone please point me in the right direction with a bit
of code? I have a workbook with 50+ worksheets, but I only need a certain 2
worksheets protected. I also want this macro to run when I close the
workbook, so that I don't have to run the macro myself (I'd probably forget).
Could someone show me how to accomplish this task? Thanks

With this code, the user would have to push "save" before he/she can close
the document in order for the protection to run. Is there a way to
accomplish the same task without having to save? For instance, if the user
unprotected the worksheet, updated cells, saved, pushed exit (code runs)
pushed "don't save" (user thinking he/she doesn't need to save again), the
worksheet would be unprotected. Thoughts?

"Josh" wrote:

Thanks!

"Gord Dibben" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets(Array("Sheet2", "Sheet3"))
ws.Protect Password:="justme"
Next ws

End Sub

Entered in ThisWorkbook module.



Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 16:41:00 -0700, Josh wrote:

Greetings. Could someone please point me in the right direction with a bit
of code? I have a workbook with 50+ worksheets, but I only need a certain 2
worksheets protected. I also want this macro to run when I close the
workbook, so that I don't have to run the macro myself (I'd probably forget).
Could someone show me how to accomplish this task? Thanks

Josh

If user says "No" to saving when closing, the BeforeClose will run anyway.


Gord

On Mon, 8 Oct 2007 17:02:14 -0700, Josh wrote:

With this code, the user would have to push "save" before he/she can close
the document in order for the protection to run. Is there a way to
accomplish the same task without having to save? For instance, if the user
unprotected the worksheet, updated cells, saved, pushed exit (code runs)
pushed "don't save" (user thinking he/she doesn't need to save again), the
worksheet would be unprotected. Thoughts?

"Josh" wrote:

Thanks!

"Gord Dibben" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets(Array("Sheet2", "Sheet3"))
ws.Protect Password:="justme"
Next ws

End Sub

Entered in ThisWorkbook module.



Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 16:41:00 -0700, Josh wrote:

Greetings. Could someone please point me in the right direction with a bit
of code? I have a workbook with 50+ worksheets, but I only need a certain 2
worksheets protected. I also want this macro to run when I close the
workbook, so that I don't have to run the macro myself (I'd probably forget).
Could someone show me how to accomplish this task? Thanks

Maybe I should ask...........did you have a problem with the scenario you
described?

Or did you just not test it?


Gord

On Mon, 08 Oct 2007 19:24:01 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Josh

If user says "No" to saving when closing, the BeforeClose will run anyway.


Gord

On Mon, 8 Oct 2007 17:02:14 -0700, Josh wrote:

With this code, the user would have to push "save" before he/she can close
the document in order for the protection to run. Is there a way to
accomplish the same task without having to save? For instance, if the user
unprotected the worksheet, updated cells, saved, pushed exit (code runs)
pushed "don't save" (user thinking he/she doesn't need to save again), the
worksheet would be unprotected. Thoughts?

"Josh" wrote:

Thanks!

"Gord Dibben" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets(Array("Sheet2", "Sheet3"))
ws.Protect Password:="justme"
Next ws

End Sub

Entered in ThisWorkbook module.



Gord Dibben MS Excel MVP

On Thu, 4 Oct 2007 16:41:00 -0700, Josh wrote:

Greetings. Could someone please point me in the right direction with a bit
of code? I have a workbook with 50+ worksheets, but I only need a certain 2
worksheets protected. I also want this macro to run when I close the
workbook, so that I don't have to run the macro myself (I'd probably forget).
Could someone show me how to accomplish this task? Thanks