+ Reply to Thread
Results 1 to 8 of 8

Event sometimes stops firing?

  1. #1
    HotRod
    Guest

    Event sometimes stops firing?

    I have code that is run when you do a cell change "Worksheet_Change"
    normally everything seems to work fine but once and a while the code just
    decides to stop firing. The only way to get it working again is to manually
    fire the "Worksheet_Change" event. Any ideas?



  2. #2
    Jim Thomlinson
    Guest

    RE: Event sometimes stops firing?

    As a WAG in your code somewhere you are using

    Application.EnableEvents = false

    As a guess the corresponding

    Application.EnableEvents = true

    is missing or your sub exits/ends prior to running that line of code... The
    other possibility is that you have generated an error which halts the code
    prior to turning the events back on.

    HTH


    "HotRod" wrote:

    > I have code that is run when you do a cell change "Worksheet_Change"
    > normally everything seems to work fine but once and a while the code just
    > decides to stop firing. The only way to get it working again is to manually
    > fire the "Worksheet_Change" event. Any ideas?
    >
    >
    >


  3. #3
    Tom Ogilvy
    Guest

    Re: Event sometimes stops firing?

    Sounds like you disable events in your code and don't properly reenable
    them. Perhaps if you posted the code, someone could give you a clue.

    --
    Regards,
    Tom Ogilvy


    "HotRod" <NOSPAM@youremail.com> wrote in message
    news:%23KwPjgNUFHA.2756@tk2msftngp13.phx.gbl...
    > I have code that is run when you do a cell change "Worksheet_Change"
    > normally everything seems to work fine but once and a while the code just
    > decides to stop firing. The only way to get it working again is to

    manually
    > fire the "Worksheet_Change" event. Any ideas?
    >
    >




  4. #4
    HotRod
    Guest

    Re: Event sometimes stops firing?

    I've actually turned of all of the Application.EnableEvents = false code in
    my code, I'm wondering if an error will cause this problem? Is it possible
    that an error is occurring that just jumps out of the macro?

    One other question I've been googling for an example of how to Sort more
    than three columns at a time and depending on where I look some sites say
    that it's not possible. This is the code I'm using below.


    Range("A" & First_Data_Row & ":AA55550").Sort Key1:=Range(First_Column &
    First_Data_Row), _
    Order1:=xlAscending, Key2:=Range(Second_Column & First_Data_Row), _
    Order2:=xlAscending, Key3:=Range(Third_Column & First_Data_Row), _
    Order3:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom



  5. #5
    Tom Ogilvy
    Guest

    Re: Event sometimes stops firing?

    Excel uses a persistent sort. You can sort on all 256 columns if you like,
    but you have to sort 3 at a time. Start with the least significant to the
    most significant.

    --
    Regards,
    Tom Ogilvy



    "HotRod" <NOSPAM@youremail.com> wrote in message
    news:u97hAEOUFHA.3544@TK2MSFTNGP12.phx.gbl...
    > I've actually turned of all of the Application.EnableEvents = false code

    in
    > my code, I'm wondering if an error will cause this problem? Is it possible
    > that an error is occurring that just jumps out of the macro?
    >
    > One other question I've been googling for an example of how to Sort more
    > than three columns at a time and depending on where I look some sites say
    > that it's not possible. This is the code I'm using below.
    >
    >
    > Range("A" & First_Data_Row & ":AA55550").Sort Key1:=Range(First_Column &
    > First_Data_Row), _
    > Order1:=xlAscending, Key2:=Range(Second_Column & First_Data_Row),

    _
    > Order2:=xlAscending, Key3:=Range(Third_Column & First_Data_Row), _
    > Order3:=xlAscending, Header:=xlNo, _
    > OrderCustom:=1, MatchCase:=False, _
    > Orientation:=xlTopToBottom
    >
    >




  6. #6
    Jim Thomlinson
    Guest

    Re: Event sometimes stops firing?

    Do you turn the events back on by setting

    Application.EnableEvents = true

    at the end of the procedure? This needs to be done otherwise events are off
    until you explicitly turn them back on... Post your code.

    As for sorting Tom is correct. If you want to sort by Column A then B then C
    then D then E then F, just do it in reverse. Sort By F then E then D then C
    then B then A. If that does not make sense then just reply.

    HTH

    "HotRod" wrote:

    > I've actually turned of all of the Application.EnableEvents = false code in
    > my code, I'm wondering if an error will cause this problem? Is it possible
    > that an error is occurring that just jumps out of the macro?
    >
    > One other question I've been googling for an example of how to Sort more
    > than three columns at a time and depending on where I look some sites say
    > that it's not possible. This is the code I'm using below.
    >
    >
    > Range("A" & First_Data_Row & ":AA55550").Sort Key1:=Range(First_Column &
    > First_Data_Row), _
    > Order1:=xlAscending, Key2:=Range(Second_Column & First_Data_Row), _
    > Order2:=xlAscending, Key3:=Range(Third_Column & First_Data_Row), _
    > Order3:=xlAscending, Header:=xlNo, _
    > OrderCustom:=1, MatchCase:=False, _
    > Orientation:=xlTopToBottom
    >
    >
    >


  7. #7
    HotRod
    Guest

    Re: Event sometimes stops firing?

    I will give it a try.

    THANKS



  8. #8
    zackb
    Guest

    Re: Event sometimes stops firing?

    Also, don't forget that if you are either stepping through your code or it
    errors out *before* you turn events back on, you will need to do so
    manually. If this is the case, you may use a routine, something like ...

    Sub ResetAppAttributes()
    With Application
    .Screenupdating = True
    .Displayalerts = True
    .Enableevents = true
    .Statusbar = False
    .CutCopyMode = False
    End with
    End Sub

    Or something like that. HTH

    --
    Regards,
    Zack Barresse, aka firefytr

    "HotRod" <NOSPAM@youremail.com> wrote in message
    news:u97hAEOUFHA.3544@TK2MSFTNGP12.phx.gbl...
    > I've actually turned of all of the Application.EnableEvents = false code
    > in
    > my code, I'm wondering if an error will cause this problem? Is it possible
    > that an error is occurring that just jumps out of the macro?
    >
    > One other question I've been googling for an example of how to Sort more
    > than three columns at a time and depending on where I look some sites say
    > that it's not possible. This is the code I'm using below.
    >
    >
    > Range("A" & First_Data_Row & ":AA55550").Sort Key1:=Range(First_Column &
    > First_Data_Row), _
    > Order1:=xlAscending, Key2:=Range(Second_Column & First_Data_Row), _
    > Order2:=xlAscending, Key3:=Range(Third_Column & First_Data_Row), _
    > Order3:=xlAscending, Header:=xlNo, _
    > OrderCustom:=1, MatchCase:=False, _
    > Orientation:=xlTopToBottom
    >
    >




+ 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