specific worksheet protection and auto run

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Thanks!

Gord Dibben said:
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
Click to expand...
 
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

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


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

Or did you just not test it?


Gord
 
Gord, I did test the code and the worksheets will be unprotected as described
below. Would it be better to run the code when the workbook opens, ensuring
that the worksheets are always protected to whomever opens the workbook?
 
User opens workbook and Sheets in the array are protected...right?

Users unprotects the sheets and makes some changes then saves
workbook.......right?

User then closes workbook and says "no" to saving......right?

The BeforeClose event will run to re-protect the sheets. At least my tests show
that happening.

But if you want to protect on opening workbook, just change the event type to

Private Sub Workbook_Open()


Gord
 
I appreciate your responses. I am using 2003 if that makes any difference.
As I understand it, because I choose not to save the changes upon exit, I am
not saving the executable code for protection, therefore my sheets are not
protected the next time the workbook is opened. At any rate, I changed the
code to Workbook_Open and it works like a charm. Thanks again
 
Josh

Presumably the workbook was saved and closed after you added the BeforeClose
code to Thisworkbook in the first place.

Closing later without saving does not alter that code and the sheets will be
protected.

Same with the workbook_open code. I presume you saved after adding that code.

No difference.

It is obvious to me you did not test but happy to hear you are sorted out.


Gord Dibben MS Excel MVP
 
Gord - I will walk you step by step and please tell me what I have missed.

Starting from the beginning.
1. Create new workbook
2. press alt+f11 to display VB
3. copy & paste code from below into ThisWorkbook
4. save workbook
5. enter data into cells A1 & A2 in sheet2
6. save
7. file->exit
8. prompted to save->select no
9. open workbook
10. select sheet2
11. enter data into cell A3 ->no protection
12. save
13. file->exit
14. prompted to save->select yes
15. open workbook
16. select sheet2, enter data into cell A4 ->protected, will not allow
17. tools->protection->unprotect sheet->enter password
18. save
19. file->exit
20. prompt to save->select no
21. open workbook
22. select sheet2, enter data into cell A4->no protection

I am a beginner to this and I am obviously missing something, so please
humbly point it out.

I do not appreciate you saying that it is obvious I did not test the code.
Now, it might be obvious that I cannot follow directions and/or I am not on
the same page as you are, but I think from the steps above, it is obvious I
tested the code.
 
Josh

You are 100% correct.

I was the one who "obviously" did not do my homework or my testing properly.

If you have unprotected a sheet and entered some data and saved, that is the
version of the file that gets applied.

The beforeclose did not run on that version so the sheet stays unprotected as it
was saved prior to closing unsaved.

Apologies for the insult and please use the Workbook_Open event.

I'll take mustard on all that crow I'm chowing down on.


Gord
 
Back
Top