+ Reply to Thread
Results 1 to 8 of 8

Worksheet_Change only fires when worksheet is unprotected

  1. #1
    Registered User
    Join Date
    12-20-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Cool Worksheet_Change only fires when worksheet is unprotected

    I have a worksheet_change event that only fires when the sheet is unprotected. I need to unprotect the sheet, run the code, then reprotect the sheet. The event should trigger a message box when the user pastes invalid data into the vertically dynamic range named "ValidationRange," which is in column J. If I unprotect the sheet manually, and remove the protect/unprotect lines in the below code, the event fires. Here is the code which currently doesn't work:

    Please Login or Register  to view this content.
    Any help much appreciated,

    Best,

    Bob

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Worksheet_Change only fires when worksheet is unprotected

    is the paste operation successful?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    12-20-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Worksheet_Change only fires when worksheet is unprotected

    JP,

    Yes, but it overwrites the validation. As I mentioned above, if I remove the protect/unprotect lines and run on the unprotected sheet, all is well, the paste is undone and the MsgBox appears.

    Thanks,

    Bob

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Worksheet_Change only fires when worksheet is unprotected

    I can't see why the code would not be triggered if the paste operation works-though you really should turn off events before you undo
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-20-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Worksheet_Change only fires when worksheet is unprotected

    Same result,

    Could there be a conflict of interest with any of the following perhaps?

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    All are placed in general modules.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Worksheet_Change only fires when worksheet is unprotected

    your passwords don't seem to match as you have 'Secret' in the change event and 'secret' in the others

  7. #7
    Registered User
    Join Date
    12-20-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Worksheet_Change only fires when worksheet is unprotected

    those are not the real passwords

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Worksheet_Change only fires when worksheet is unprotected

    worth checking ;-)

    I can't see anything there that would stop the code from running-have you tried adding a breakpoint to test if the event is even triggered? if the paste happens, then cells are changed, so I'm puzzled as to how it could fail to fire

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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