+ Reply to Thread
Results 1 to 7 of 7

ThisWorkbook macros stopped working

  1. #1
    Registered User
    Join Date
    10-07-2008
    Location
    United Kingdon
    Posts
    7

    ThisWorkbook macros stopped working

    I have several Excel 2000 workbooks with vba macros in ThisWorkbook controlling what happens when a workbook is opened, activated or closed. These workbooks are in use by several users on different PCs and all macros were working fine with no problems whatsoever.

    Suddenly, they no longer work on any PC for any user. For now I have had to use the old method of Sub Auto_open() and Sub Auto_close() in the main module.

    So I have two questions:
    1. Does anyone know of a security patch or similar that could stop macros in ThisWorkbook from running?
    2. Does anyone know of an alternative to ThisWorkbook "Sub Workbook_Activate()" macro that I could use in the main module?

    Thanks
    Last edited by smited01; 10-09-2008 at 08:21 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    Hi - my initial reaction would be to double-check whether any changes to the macros have been made; and in particular whether there is any reference anywhere to:
    Please Login or Register  to view this content.
    ... anywhere in the code. If there is, and the code is exiting (even if it's been trapped), look to make sure that as part of the trap / exit process that this has been turned back on. It may not be this, but it's the most likely suspect
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  3. #3
    Registered User
    Join Date
    10-07-2008
    Location
    United Kingdon
    Posts
    7
    Hi MatrixMan

    There is an EnableEvents = False in the main module which isn't called until the users run an import routine (EnableEvents = True is at the end of the same routine). This shouldn't stop a ThisWorkbook Sub Workbook_Open() from running, should it?

    Eddie

  4. #4
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    This is what the EnableEvents property does; it stops event macros from running (like Workbook_Open or Worksheet_Change etc). IF it is set at the start and the code abends for some reason and you've not trapped the error in such a way as to ensure it is turned back on before exiting, then this will be the cause. I suggest you step through it and see what's happening.

    By the way, if events are not currently being called as they should, then reinitialise the application property from the Immediate window (Ctrl+G) using
    Please Login or Register  to view this content.
    ... as you would in code normally. Hope that helps. MM.

  5. #5
    Registered User
    Join Date
    10-07-2008
    Location
    United Kingdon
    Posts
    7
    Hi MM

    I've retrieved an old version that doesn't have any reference to EnableEvents = False and the vba procedures within ThisWorkbook that run on open and before close work fine. So it does seem that is the cause.

    What I don't understand is why EnableEvents = False is having an affect before it has even been called, especially since I have error traps in my code that, if an abnormal end occurs, re-enable events (EnableEvents = True).

    Eddie

  6. #6
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    hi eddie ... it only takes 1 time for this to be set (such as stepping through your code and stopping before getting to the normal exit) .. then you have to force it back on as below. Either you've not trapped whatever is causing the abend and bypassing the normal exit process (which is why I don't like OnError Resume Next!) ... Or you've been stepping through and stopped the code ... either one sound familiar? As i said, you can turn this back on in the immediate window and re-run it to see what's happening ...

  7. #7
    Registered User
    Join Date
    10-07-2008
    Location
    United Kingdon
    Posts
    7
    Hi MM

    What you say makes sense. I've traced the problem to a stray line of code in another workbook that disabled events. Thanks for your help - problem sorted.

    Eddie

+ 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. Excel 2007 macros have stopped working
    By altidude in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2009, 02:53 PM
  2. Macros stopped working on one server only
    By gerelda in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2008, 08:36 PM
  3. Macros are not working on a Mac
    By vjboaz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-23-2008, 03:25 PM
  4. VBA Stopped Working!
    By SamuelT in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-16-2007, 11:12 AM
  5. My formulas stopped working
    By gmoexcel in forum Excel General
    Replies: 1
    Last Post: 11-22-2006, 10:59 PM

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