+ Reply to Thread
Results 1 to 9 of 9

Worksheet Change and Worksheet Activate Events Reprotecting Automatically

  1. #1
    Registered User
    Join Date
    10-28-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    16

    Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    Have a worksheet where I'm trying to code in three things:

    1) An Auto-Sort once data is entered in columns 1-7
    2) A worksheet change event that runs my main macro when data in the table is changed
    3) A security feature that locks cells in column 7 when column 10 = "Yes"

    I've used the following code to achieve this:

    Please Login or Register  to view this content.
    The problem is for the "Approver", who has the password and will be the one entering "Yes" in column 10. Right now, each time there's an entry the sheet re-protects. So were the approver to have 20 rows to approve, they'd need to unprotect the sheet manually 20 times which is very burdensome. I've considered having a login that would disable events, but I want the worksheet change event in particular to be active while the approver works. Any suggestions for a workaround?

  2. #2
    Registered User
    Join Date
    09-20-2012
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    If the Approver is always the same person, you could use the Environ("Username") to check who is doing the approving. Something like:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-28-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    Unfortunately there are two approvers. What bothers me is that I can't even figure out which part of the code is re-protecting the sheet. At first I thought it was the worksheet change code, which calls the Auto_Open macro. Even though I don't see where that code activates a different worksheet, I figured that Sub Auto_Open was in turn triggering my Worksheet Activate event. So I broke the first bit of code up into separate events, one for the sort and one for the worksheet change. Then I changed the range from A:L to A:I, so I could (I thought) enter "Yes" in column J without triggering Auto_Open and reprotecting. But it reprotected anyway. That stumped me. What bit of code is doing that? My only explanation is the conditional formatting I have in the sheet. When I enter "Yes" in column J the previous columns change their fill color. Could the color change alone trigger the Worksheet change event? Does any of this make sense?

  4. #4
    Registered User
    Join Date
    09-20-2012
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    Sounds like you are getting caught up chasing ghosts without knowing what's happening! Do you know how to debug? Go to the first line in code Application.ScreenUpdating = False and put the cursor there. Then push F9, this will put a breakpoint in and highlight the line brown. What happens is you can execute the code 1 line at a time and trace exactly whats happening, pretty good right? Now change something on your sheet and the code window should pop up with the first line highlighted. At this point you can use F8 to step through the code, executing line by line to see what is changing and when.

    To answer one of your questions: No, changing a cells colour does not set off the change event, its only when you change data in a cell (or many cells).

    The other thing is, you could set it up so that it doesn't protect for 2 people as well with:

    Please Login or Register  to view this content.
    The protection can be weird in MS Excel sometimes, I have recently found a MS Excel bug where Worksheet.Protect doesn't work when the workbook is closing... but as it takes $249 USD to report a bug to Microsoft, I won't be reporting it!

  5. #5
    Registered User
    Join Date
    10-28-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    That is really helpful thanks so much. So that shows me that it's the Worksheet Change Event, and not the Worksheet activate one, that is running when I enter data in Column 10. I'm still not clear on exactly why. My goal with the code was to have the data auto-sort once data was entered in columns 1-7. Again, the code is:

    Please Login or Register  to view this content.
    Any thoughts on why that would run when I enter a "Yes" in column 10, even if the other columns are all blank?

  6. #6
    Registered User
    Join Date
    09-20-2012
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    OK, the Worksheet_Change event will fire whenever any data changes in your sheet, regardless of what column it is. You code suffers from a distinct lack of End If's which isn't a problem as long as you can keep track of what's happening... but even I find it hard from reading it. But what it looks like you are missing is that the ActiveSheet.Protect part will ALWAYS run. That's because its not part of the proceeding If statement. What you need is this:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-20-2012
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    A little lesson in VBA there: If statements can be used in a single line, like you do on your last two. But in such cases they can only be used to execute ONE statement. In your last two, that works fine as you are only executing an Exit Sub, a single statement.

    However, if you are executing multiple statements then you need to use If statements like this:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-28-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    Thank you so much for the help. As you can tell from my username and questions I don't really know VBA, I've just managed to find pre-written code and manipulate it to do what I want. But learning things like this is why I tackle problems like this in the first place. So I added the End If to the code as you have above and it spits back a Compile Error. Specifically, it says "End If Without Block If"

  9. #9
    Registered User
    Join Date
    09-20-2012
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: Worksheet Change and Worksheet Activate Events Reprotecting Automatically

    No worries, I encourage experimentation!

    You are getting this error probably because you haven't put an enter mark after:

    Please Login or Register  to view this content.
    Put your cursor after the "Then" and hit the "Enter" button!

+ 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