Protecting worksheet via vba. Code works but when i save,close and reopen i get error.

I have this file (see enclosed). There are multiple sheets in the file but consider only the 3rd worksheet for this example.

I run the macro in the module code which protects the cells C2 and D2. I verify that the vba code did indeed protect the cells. I then close the file and then re-open the file. After i reopen the file i go to cell B2 and type in a value.......i then get a runtime error which shows up in the change-event code for worksheet 3.

Any ideas?

That wont work for me.

If i change the contents of the one of the cells in the status column then the last two column of cells along taht same row are supposed to clear.

i am really having trouble with this change-event + protection requirement.

Very tough.

Basically, tehre are 4 cols which appear at the end of a dataset (which i did not include in test file .....as dataset is random rows and cols of data). These 4 cols are the last four cols in the worksheet. However, the header row may not begin on row 1 it may begin on row x.

For now lets assume it begins on row1. If there are values in say the status, comments, date and Reviewer's initials cols.......for row2 AND then the user decides to change the status value to something else then the date and reviewer's initials will be cleared out.

I have (thanks to this site and help from others) change-event code in the worksheet to make this happen.

I also want to protect the date and reviewers initials cells so the user cant inptu values only VBA.

Every thing works great.......until i close and then reopen the file. I am not sure if the problem is the change-event code or if its the protection that i put in place. Because the reason i get the runtime error is because its saying i have to unprotect sheet.

Does this help explain it? if not please let me know and i will try to explain it better.

Ok, i figured something out but i dont understand why it works.....or if it will work long term so i am hoping someone can help me here.

I added this bit of code to the change-event vba code on workhsheet #3

Please Login or Register  to view this content.

After adding this i can close the file, reopen it change cell B2 contents and i dont get a runtime error and everything seems to be functioning as i want..........


Any idea why? I mean why is the above code necessary?


All the code in the event-change worksheet #3 is shown below. Note: I verified that if i do take out the ".protect user...." code , resave file, close it and then reopen it i then go to cell b2 (on sheet 3)and try to change contents i get a run time error saying sheet is protected. So the added code is doing something.....just dont understand what.

Please Login or Register  to view this content.

ok, when i add this extra code to my "real" data set ...does not work. So there is obviously too much going on here that i just dont understand about change-event code and / or protecting sheets.

Help?

OK, totally confused on this one but i think i figured out why my code did not work.....anyway, i have the same question i asked in a previous post at 7:40am.......i think if i know why this works then i might have a better understanding......can someone , anyone.....help?

ok,....see enclosed file. Just open the enclosed file. On sheet "Sheet3".....goto cell B2. Type in junk.......and you should get a run time error.

Now.......

go into the vba code for "Sheet3" and change the change-event code so this code (which should be currently commented out) is now NOT commented out.....

Please Login or Register  to view this content.

Now re-save the file.

Now re-open the file, goto "Sheet3", goto cell B2 and type junk........this time you dont get the run time errorl.........

my question is WHY? What does this added code do?

does anyone know the answer?