Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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? "Gord Dibben" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 On Tue, 9 Oct 2007 05:02:01 -0700, Josh wrote: 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? "Gord Dibben" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 "Gord Dibben" wrote: 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 On Tue, 9 Oct 2007 05:02:01 -0700, Josh wrote: 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? "Gord Dibben" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 On Wed, 10 Oct 2007 17:49:03 -0700, Josh wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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. "Gord Dibben" wrote: 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 On Wed, 10 Oct 2007 17:49:03 -0700, Josh wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
specific worksheet protection and auto run
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 On Thu, 11 Oct 2007 20:25:01 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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 On Wed, 10 Oct 2007 17:49:03 -0700, Josh wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto filters and worksheet protection | Excel Worksheet Functions | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
Cell protection & Auto Filter | Excel Worksheet Functions | |||
Worksheet protection is gone and only wokbook protection can be se | Excel Discussion (Misc queries) | |||
auto protection of cells | Excel Worksheet Functions |