Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    WS Change stops working (2000+)

    Hi All,

    In <post#=345873>post 345873</post#> and related posts of the thread, I observed that the Worksheet_Change event seemed to stop working. This was in a workbook that John sent me for entering times for music tracks.

    I am trying to work on my own workbook for this. I've observed the same thing in my own workbook. I can't tell when it will stop working. When it does, closing the workbook and re-opening (without closing Excel) doesn't help. But closing Excel and re-opening it with the workbook does restore the execution of Worksheet_Change.

    How do I know it stopped working? I had some break points that should have been encountered. Even simpler, I put a Msgbox "Hello" stmt as the first executable stmt. When it wasn't working, this msgbox wasn't appearing.

    I do need to use Worksheet_Change to trap the entry and reformat it, as discussed in the thread mentioned above.

    Any clues as to why it's not working or, rather, stops working and how to get around this?

    TIA

    Fred

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

    Re: WS Change stops working (2000+)

    It sounds like you have a statement like this:

    <pre> Application.EnableEvents = False
    </pre>


    somewhere in your code. It sounds like that statement is being executed and the corresponding statement:

    <pre> Application.EnableEvents = True
    </pre>


    is not being executed.

    The next time this happens, try going to the immediate window in the VB environment and execute the above statement. If that gets the Worksheet Change event working again, then this is very likely what happened.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: WS Change stops working (2000+)

    To exapnd on Legare's comments:
    I have observed this phenomenon on several occasions and usually happens either when:

    stepping thru code (using F8) executing the enableevents = false, but then stopping the code before the enableevents = true is run

    Running the code and getting a runtime error and execution is halted before the enableevents = true is run.

    The enableevents = false is "application code" so it is for all excel not just the workbook that calls it. It tells excel to "ignore" any and all "event-triggering".

    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: WS Change stops working (2000+)

    Legare and Steve,

    Thks for the responses.

    I am definitely not stepping thru the code with F8. But I have breakpoints set at strategic places. I do break the code execution if I get to a breakpoint and have gotten enough info. And there are Application.EnableEvents = False/True statements. So Legare's and Steve's first observation might hold (I don't think I had any run time errors).

    But I don't think that's the problem.

    The code I'm using is a mod to Chip Pearson's code on his web site for entering dates and times (see this example ). In it, he has an App.EnableEvents=False before a Select Case stmt (after some initial checks) and an App.EnableEvents=True after the Select Case (in addition to an ...=True in an error handler). I don't think I've ever gotten into that section of code yet. I also can't see entering that section without leaving that section; so what's set to false should be set back to true. He does have a Case Else if none of the other cases are executed and the code for the Else is Err.Raise 0. I presume this raises the error handler, which prints an error msg and then should execute the App.EnableEvents=True.

    But I'll keep your suggestions in mind and use the immediate window to reset App.EnableEvents if this happens again. I still have to mod Chip's Select Case code for my particular case (an adaptation of something that Jan Karel posted back to me in the other thread on entering music track times). So I'm certainly not attributing any of my problems to Chip since my work is still evolving.

    If I run into trouble, I'll post back (or maybe just post the completed workbook if anyone's interested).

    Thanks again.

    Fred

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: WS Change stops working (2000+)

    If you set a break point and then end the code while the enableevents =false and before the enableevents = true it is the same affect of stepping thru and stopping it or getting an error and stopping it.

    If you STOP the code (by any means) after the events are "disabled" and before they are reenabled, the events are disabled for the entire excel session (unless you run other code that enables the events)

    Steve

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WS Change stops working (2000+)

    Along with the other recommendations you've received, I'd also add a trick that I use all the time. Since you can never be sure that events are indeed enabled when your workbook is opened, I always use both Auto_Open() and Workbook_Open(). Auto_Open() will run even if events are disabled so I use that to test if they are disabled and then 'kick-start' the Excel macros by re-enabling enents and then call Workbook_Open() as usual. I use a global boolean to tell me if Auto_Open() ran or not. Like this:
    <pre> 'in Thisworkbook
    Private Sub Workbook_Open()
    Call WBOpen
    End Sub


    ' in a module
    public DidWBOpen as Boolean
    Public Sub Auto_Open(Optional noArgs As Boolean = True)
    If DidWBOpen Then Exit Sub
    Application.EnableEvents = True
    Call WBOpen
    End Sub

    Public Sub WBOpen(Optional noArgs As Boolean = True)
    DidWBOpen = True
    ' do your init code here
    End Sub</pre>

    Deb

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: WS Change stops working (2000+)

    Thanks Deb.

    I've been paying close attention to where I abandon the code and it definitely seems like all the suggestions about abandoning after an EnableEvents=False were on the mark. I'll probably incorporate your code also as an extra safeguard.

    Fred

Posting Permissions

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