+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

    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?
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,875

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

    I am not entirely sure what you are doing, possibly

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    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.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,875

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

    Maybe explain what you code is supposed to do,
    When you enter something into B2 what is supposed to happen?

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    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.

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    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.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    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?

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    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?

  9. #9
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,875

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

    Just keep the explaination simple.

    What heppens when you enter something in B2, or whatever cell.

    Your sample should show how your original layout is, you have left columns out

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    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?
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,505

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

    does anyone know the answer?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1