Protecting all sheets with a password.

[Nitro2481]

Hi guys,

I currently have the following code in every sheet in my workbook. When someone was helping me with something else they questioned why I have this in the worksheet as it would never execute.

Please Login or Register  to view this content.

I then searched for an answer to how to protect all sheets with 1 code and came across this

Please Login or Register  to view this content.

As you can see this allows for a password to be input into the protection which I would like. My 2 questions are, is there somewhere on this code that you type what you would like the password to be? I thought it might be here

Please Login or Register  to view this content.

but that didn't seem to work and also where do I stick this code. I guessed it was in a module which I did and deleted the first code from each individual sheet but it didn't seem to work.

[cytop]

As explained in the other thread

Another question is why does there seem to be a Workbook level event (Workbook_BeforeClose) in what seems to be a Worksheet class. This will never execute

Workbook level events are only raised in the ThisWorkbook class module. The code itself is syntactically correct so the compiler doesn't object but Workbook events are not raised in a Worksheet class, so the code would never execute.

The ProtectAll code goes in a standard code module so it can be called from anywhere in the code.

Most of the original code is not necessary as it only asked the user for the password twice and then ensured both entries match. This is not needed if you are using code to set the password, there's no possibility of entering an unknown password in error as it will be visible in the code.

Please Login or Register  to view this content.

[Nitro2481]

Workbook level events are only raised in the ThisWorkbook class module.
The ProtectAll code goes in a standard code module so it can be called from anywhere in the code.

Hello again,

Must be sick of me at this stage.

I have put your code in Module 8. Deleted my codes out of each sheet. Unprotected a few sheets. Closed the workbook and opened it again and the sheets remain unprotected. I have noticed from your previous help that usually when there is a module there is a trigger for the module somewhere else. In our proper name one there was a trigger on each individual page. You have mentioned the This Workbook. Should I be putting something in there aswell.

[cytop]

All depends... which seems to be the standard answer for VBA.

Code in a standard module is just that, code. It can only run when it is called from another procedure or event. Code in Event Handlers, on the other hand, run automatically when that event occurs (WorkBook_Open, Worksheet_Change etc)

If you need to protect (or Unprotect as well with a very minor change) from anywhere in the code then you leave it in the module and simply call it from whereever. That means you add a call to it in the Workbook_BeforeSave event

Please Login or Register  to view this content.

If you are certain that you only need it when the workbook is being saved then you could move the code to the Workbook_BeforeSave event handler.

Please Login or Register  to view this content.

[Nitro2481]

Hey,

I have copied how my This Workbook looks now and have attached a copy of my wb. I am very nervous about code as it is the most detailed. I have added module 8 and also have added your code in red into this workbook. I have also noticed that there is some similar code that protects sheets with no password highlighted in orange. I imagine this might be a conflict. You will also notice that in some of the sheets there is a code to unprotect, do something and protect. I assume that ight be a conflict with this password protect now also. An example of this is sheet 5 where when someone logs in a password 1234 is inserted in a cell to unhide columns or else deleted to hid columns. User john, Passwrd test

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[cytop]

As you said...

that in some of the sheets there is a code to unprotect, do something and protect. I assume that might be a conflict...

Situations like that are the most difficult to support on a forum. Make a change in one place and you have no idea of the effects on other parts of the code - especially when the code base is large.

Attached is a slightly reworked (understatement) version of your workbook - there are too many changes to list but particular attention to the Workbook Open & BeforeSave events; The Validate password procedure in the userform (Yeah, I know - nothing to do with your problem but it was easy to re-jig); the Change event in the first 2 worksheets.

The biggest change is all the Protect & Unprotect statements are gone, replaced by 1 procedure called in the Workbook Open event - I'll leave you have a look at it but bear in mind the sheets are protected but without a password - That's easy enough to add and there's only 1 place to add it to.

For info, I don't think I've 'lost' any of the functionality, but the number of code lines is down from just over 700 to just under 500, so a 30% reduction ain't too bad...

(Why? She's watching Tennis!)

[Nitro2481]

Hey,

My god, I can see you put a lot of work into this. Thank you so much. However I have copied every one of your codes sheet for sheet. I have added the new levels sheet. I have renamed the 2 sheets you renamed shtBASS & shtHIN, I copied your modules and dropped my modules that you had dropped. I also added the newMod that you have and copied the "This Workbook code" and User form code. When I open yours it gives me the option to select different users and then enter a password. When I open mine it says unprotect sheet and is asking for a password. I am not aware of any password. when I enter a wrong password I'm given the option to debug. Then it highlights the newMod module with the bit in red below Highlighted in yellow.

Please Login or Register  to view this content.

[cytop]

I didn't quite mean for you to move the code to your workbook immediately - it was meant as an example only. Moving code like that means the comment in my post above applies: "Make a change in one place and you have no idea of the effects on other parts of the code - especially when the code base is large".

However, it seems the issue is just that there are existing passwords on your worksheets which Excel is prompting for. As as my code does not include passwords (for simplicity, just for the moment), it may work if you manually remove the passwords first.

But what I would suggest you do is play with the copy and make sure it contains the functionality you expect for the sheets that do exist, and works correctly with no issues about certain cells not updating "automatically" (You never did explain what this means). Once you are happy the changes work as intended then you can start to move the code to your working copy.

A small point about "renamed shtBASS & shtHIN". When referring to worksheets, the original code referred to both the Worksheet Tab name and the CodeName. Using the CodeName is preferable as code that uses the sheet name (from the worksheet Tab) will error if a sheet is renamed. The CodeName can only be defined in the Properties box for the worksheet in the VBA editor - you may already know this, just making 100% sure. The renaming was just a start to standardise things.

And it didn't take too long, about 90 minutes that otherwise might have been spent begrudgingly watching Tennis... The choice was clear

[Nitro2481]

Ha ha have to say not a fan of the tennis. Like yours my wife loves it. I saved a copy of my sheet and transferred it to the copy so no damage done. Yes there were a few passwords in the original sheet as your code had actually worked by inputting on some of the sheets. Most of the things I was worried about clashing haven't as I was able to deleted all the duplicate "protect" bits as with the criteria I have selected I can do most of my work without unprotecting the sheet. My only issue at the moment is this bit. Is there a way that the code can input the password on unprotect automatically. ActiveSheet.Unprotect "Mypassword" or something thats actually possible.

Please Login or Register  to view this content.

[cytop]

Yes, just pass the Password

Please Login or Register  to view this content.

If there is no actual password, that's not a problem, it ignores any password provided unless it is needed.

[Nitro2481]

Your a legend. Sorry last questionis there a way to save it afterward.
I tried
Sheets("Accountant").Select
ActiveSheet.Unprotect Password:="x" '// for the slow learners
ActiveSheet.Unprotect "x" '// for the slow typists
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
ActiveSheet.Save

&

Sheets("Accountant").Select
ActiveSheet.Unprotect Password:="x" '// for the slow learners
ActiveSheet.Unprotect "x" '// for the slow typists
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
.Save

in the hope that save will reprotect the sheets with the password but they dont work.

[cytop]

Confused.

Please Login or Register  to view this content.

You don't save a worksheet, you save a workbook.

Please Login or Register  to view this content.

And assuming you are using the code from my sample in the BeforeSave event

[Nitro2481]

Sorry Rookie mistake. I have done the below and the sheets save but they do not use the password however if I press the save icon myself they all save and require a password???

Please Login or Register  to view this content.

[cytop]

Please Login or Register  to view this content.

Ermmm - I didn't actually mean for you to use both lines. They are just 2 variations of the way the code can be typed. Sorry if that wasn't clear.

myself they all save and require a password

Not sure what you mean - if you're getting a prompt for the password that's because the code called by the BeforeSave event protects the sheet without a password but if they're already protected it needs the password to continue.

[Nitro2481]

I meant when I press the save button all the sheets save and when i go to unprotect it looks for a password, but with the thisworkbook.save at the end of the macro above it is not saving the sheets at the end of the macro. I haven't used your rework code yet as I need to go through it in detail before transferring it over. I tried copying your Before save bit just now and when I run that above macro I'm getting this error. But I suppose that bit of your code will not work without changing the rest of it too?

Please Login or Register  to view this content.

[Nitro2481]

This is how my before save looks now.

Please Login or Register  to view this content.

[Nitro2481]

Hey ,

So I have tried your new rework. Works quite well. Noticing a few bugs. I'm just writing them all down here so I don't forget them. Sorry I'm throwing a lot at you
1. It doesnt reprotect on save
2. When different people login I want them to land on different pages. At the moment it just lands on the first sheet. As it happens that fine for case 2 & 3 but I would like case 1 to open on sheet 29 rather than 13
3. When case 1 logs in the numbers 1234 are input into a certain cell in 3 different sheets. (2, shtBASS, shtHIN) therefore uniding columns. When case 2 or 3 log in these three cells should be cleared of the 1234 therefore hiding the columns. This does not appear to be working. I can see the numbers are being put in and deleted correctly but (I think I'm saying this correctly) its not triggering the event????
4. Module 5 is not protecting all sheets after it is run. (thats probably linked to point 1)
5. This is a strange one, in sheet 2 if I make AB "block" this triggers information to be put to shtBASS. Part of this moved information triggers the date code and the date gets put into cell A. Perfect. However in sheet 11 there is a similar trigger (Column L) to transfer to shtBASS and for some reason this does not trigger the date. The very same is happening for Sheet 2 AC to shtHIN (working) sheet 15 (column J) to shtHIN (Not working)
6. Last thing, while you were working on the rework for me (again thanks so much) somebody gave me a code to input the date in A if D has text, but also to clear A if D is cleared. This worked fine in my old one. I'm not sure how to add that to your code. I have replicated it in sheets shtBASS & shtHIN as they are identical "if" codes but I am not able to replicate it in sheets 11,15,17 as these are "case" codes and not "if" codes.

Please Login or Register  to view this content.

I have attached a full version of my workbook. User John password test

[cytop]

Comments in a new sheet in the workbook (and I've just realised that'll be hidden by the code)...

Didn't get around to #5 - time and all that.

[Nitro2481]

Hey,

1. It doesnt protect on save. I have attached the This Workbook code. Going by my extensive VBA knowledge (ahem, that you could write on the back of a stamp ) I cannot see the bit calling for save.
2. When different people login I want them to land on different pages. At the moment it just lands on the first sheet. As it happens that fine for case 2 & 3 but I would like case 1 to open on sheet 29 rather than 13. This is working now
3. When case 1 logs in the numbers 1234 are input into a certain cell in 3 different sheets. (2, shtBASS, shtHIN) therefore unhiding columns. When case 2 or 3 log in these three cells should be cleared of the 1234 therefore hiding the columns. This does not appear to be working. I can see the numbers are being put in and deleted correctly but (I think I'm saying this correctly) its not triggering the event????. Working now
4. Module 5 is not protecting all sheets after it is run. (thats probably linked to point 1) Working now
5. This is a strange one, in sheet 2 if I make AB "block" this triggers information to be put to shtBASS. Part of this moved information triggers the date code and the date gets put into cell A. Perfect. However in sheet 11 there is a similar trigger (Column L) to transfer to shtBASS and for some reason this does not trigger the date. The very same is happening for Sheet 2 AC to shtHIN (working) sheet 15 (column J) to shtHIN (Not working) This was happenong on my onld one and someone pointed out that one of the event handlers was set to false. He showed me how to stick a true one and another false one on the sheet sending the info but this new code is completely different to the old one
6. Last thing, while you were working on the rework for me (again thanks so much) somebody gave me a code to input the date in A if D has text, but also to clear A if D is cleared. This worked fine in my old one. I'm not sure how to add that to your code. I have replicated it in sheets shtBASS & shtHIN as they are identical "if" codes but I am not able to replicate it in sheets 11,15,17 as these are "case" codes and not "if" codes. Working now

Please Login or Register  to view this content.

Also do I still need module 8

Please Login or Register  to view this content.

now that you have the lock module?

Please Login or Register  to view this content.

Probably stupid question??

[cytop]

I cannot see the bit calling for save

You're looking i the wrong place. Nothing in the class is called by code, or at least any of the VBA code in the workbook.

What happens is the Workbook Save events declares a new instance of the class. The class contains an Initialize events which is called automatically by VBA when the class (or an instance of the class) is created. You do nothing except declare and initialise the instance.

Please Login or Register  to view this content.

That line caused the Class_Initialize event to run which creates 2 collections. 1 contains a list of all the locked worksheets, the other a list of the unlocked. The code in the Save event then continues to save.

When the class is destroyed

Please Login or Register  to view this content.

or the procedure exits, meaning cSL goes out of Scope (It was declared local to the procedure only) and is destroyed by the garbage collection, the class Terminate events fires. This called the SheetProtection procedure for each sheet setting the protection back to what it was when the class was initialised.

I know it's probably over the top - but rather than trying to understand all the in and outs, I just saved the Lock status of each sheet at the start of the procedure and reset it back after. If particular sheets are unlocked after the Save then they were unlocked before...

[Nitro2481]

So if I unlock a sheet I have to lock it back manually. Cool.

I better start thinking up new problems or you'll be stuck watching the tennis

Seriously though, I really appreciate all the support over the few days