+ Reply to Thread
Results 1 to 6 of 6

why a Worksheet Change Event during a macro

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    why a Worksheet Change Event during a macro

    I am working on a phenomenal workbook created by a former employee. There are 20+sheets used to hold constants and collected data. There is a summary sheet to display it all.

    He has a Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) that is active while the mega macro is running. Consequently when you step through it take far longer than it should. (I put a break at the end of the Workbook_SheetChange so I could F5 every time it ran but it's still a pain.

    (1) is there ANY benefit to having a Workbook_SheetChange event firing while a macro runs?

    (2) if I add an Application.EnableEvents = False then turn back on at the end could I cause other problems?

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,984

    Re: why a Worksheet Change Event during a macro

    It depends on whether you need the change event, or other events, to run in response to the main macro. If not, disable events - just make sure to re-enable them again.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,290

    Re: why a Worksheet Change Event during a macro

    If the sheet change event is monitoring all changes on all sheets to create an audit trail, then maybe it needs to be there. What does it do?


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    04-24-2007
    Location
    NYC
    MS-Off Ver
    2k3/2k7/2010
    Posts
    270

    Re: why a Worksheet Change Event during a macro

    If the macro is take a long time and runs on every data entry it is probably doing a lot of data validation.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,290

    Re: why a Worksheet Change Event during a macro

    Post the code. We cannot guess at what it is doing.

    Even better, post a sample workbook.

  6. #6
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Re: why a Worksheet Change Event during a macro

    I can't post the code it belongs to my employer. I'm going to disable events and see what happens.

    This workbooks is actually a template. All it does it takes user entries and calculate a cost. Then they save as for reference.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Stop Worksheet change event from interfering with macro code
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2013, 09:11 PM
  2. Worksheet Change event ignore change event
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2011, 12:29 PM
  3. [SOLVED] Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 AM
  4. How to keep ONKey's macro on Worksheet Selection Change Event
    By mikeburg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2005, 08:59 AM
  5. [SOLVED] Worksheet Change event code moved to Worksheet Calculate event... and it's not working
    By KimberlyC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2005, 06:05 PM

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