Results 1 to 4 of 4
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Using Sheet Change Event (2000+)

    I have a sheet that is using the Change Event of the sheet to determine if a cell is altered.

    If a certain cell is altered, then the value is used as part of a fill routine on the same sheet.

    This is fine, BUT, the change event also seems to be triggered when a cell is changed using VBA code as well.

    Is there anyway to stop the change event being triggered when a cell is altered using code ?

    I thought that comparing against the target would fix the issue, but it doesn't.
    Suppose the original target cell was C12
    This triggers the event and I test in the code that target is C12.

    Later I change C13.
    This appears to trigger the event.
    All would be OK except the target is still returned to the procedure as C12.

    OK I thought, turn of Automatic Recalc at the start of the process.
    NOPE!

    It all appears to be ok, but the routine is called recursively as far as I can see
    (with the same Target each time)
    I put in an error trap and this stopped it looping continuously, but it is still slow to execute
    due to the repetition.

    OK, I could get the users to enter the value and then press a button but that isn't what was requested.

    Any ideas?

    OR do I learn to live with something that works OK, but I've had to use a work around.

    I am more than happy to be both educated and enlightened.

    If the actual code would be useful, I can post that in another e-mail.


    Thanks
    Andrew

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Sheet Change Event (2000+)

    Since it sound like you have some problems other than what you are describing, it would help if we could see your code. Could you post the code?

    To answer your specifc question, yes, you can prevent events from being fired from VBA code with:

    <code>
    Application.EnableEvents = False
    ' The code that is triggering the event.
    Application.EnableEvents = True
    </code>

    If there is any chance of an error occuring, the code between those statements should be protected with error handling since it would leave events disabled if an error occurs between those statements.
    Legare Coleman

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Sheet Change Event (2000+)

    Thanks.

    I'll try this solution out first and see if it resolves the problem.

    Looks like it will.

    If it doesn't. I'll post the code
    Andrew

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Sheet Change Event (2000+)

    YEP.

    That worked just Fine.
    Many thanks.
    Andrew

Posting Permissions

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