Macros stop working an unknown reason.

[VytautasM]

Good day All,

Have a work project with which I am at my wits end. In short. There 16 worksheets for 16 different users. Each week has a new workbook. Every workbook is uploaded to a Sharepoint page. The workbook is restricted it has locked cells, protected and hidden sheets. Also it contains workbook_open, worksheet_change and other macros.

The problem. At random all macros in a users workbook stop working for no apparent reason. For some this problem does not exist. For some this problem occurs on random weeks. Other weeks works fine. There is no error upon opening the workbook, but workbook_open and worksheet_change macros do not work. For some upon opening the workbook, workbook_open macros work, but as soon as they trigger a worksheet_change macro, the workbook goes into auto recovery mode, after which ALL excel files crash. After they inform me of the problem, I open the workbook from my PC with no problems, everything working as intended. On some cases re uploading the workbook and telling the user to restart their pc seems to solve the problem.

I have gone threw my code and have not found anything to cause the problem (of course my knowledge is limited so might be wrong there). If there was something wrong with the code the problem would be constant every week for every user and this is not the case. Searched the internet, but have not found anything to solve the problem. Do not really know how to handle the problem since a cannot replicate the problem.

Any help would be appreciated.

 

[Joe4]

Could you please give an example of an error handling code ?

Usually, you put a line like this at the beginning of a procedure:

    On Error GoTo err_chk

which basically tells it if an error happens, go to the error handling code.

Then, at the very bottom of the procedure, before the "End Sub", you mught have a block something like this:

    Application.EnableEvents = True
    Exit Sub
    
err_chk:
    MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True

The "Exit Sub" is there so if you get to that line naturally, without any errors, it will exit the sub and not run the error handling code.
Our error handling code in this example has two lines, one to return the details of the error to the screen in a Message Box, and one to re-enable events.

You can actually use this to capture specific error codes, if you want too. For example, if you did no care about "1004" errors, you could do something like this:

err_chk:
    If err.number = 1004 Then
        err.Clear
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
        Application.EnableEvents = True
    End If

So if error 1004 is encountered, it tells the code to clear the error and continue on with the Macro.
That is more traditionally how error handling is often used, to "handle" specific errors. But in this case, we simply want to make sure that if any error is encountered where it is kicking you out of your code, that we make sure that events are re-enabled.

 

[VytautasM]

Usually, you put a line like this at the beginning of a procedure:

    On Error GoTo err_chk

which basically tells it if an error happens, go to the error handling code.

Then, at the very bottom of the procedure, before the "End Sub", you mught have a block something like this:

    Application.EnableEvents = True
    Exit Sub
  
err_chk:
    MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True

The "Exit Sub" is there so if you get to that line naturally, without any errors, it will exit the sub and not run the error handling code.
Our error handling code in this example has two lines, one to return the details of the error to the screen in a Message Box, and one to re-enable events.

You can actually use this to capture specific error codes, if you want too. For example, if you did no care about "1004" errors, you could do something like this:

err_chk:
    If err.number = 1004 Then
        err.Clear
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
        Application.EnableEvents = True
    End If

So if error 1004 is encountered, it tells the code to clear the error and continue on with the Macro.
That is more traditionally how error handling is often used, to "handle" specific errors. But in this case, we simply want to make sure that if any error is encountered where it is kicking you out of your code, that we make sure that events are re-enabled.

Thank you, Joe4. Will try to implement error handling.

As I was thinking where the

Application.EnableEvents = False

might be triggered. This occurred to me. I prepare each file with a separate macro, it opens a Master File, changes 1 cell in that file (week number) and then "saves as" the file with a certain name. And this cycle continues 16 times. Might the problem be that when changing that 1 cell the

worksheet_change

macro is triggered it goes threw a certain amount of code triggering

Application.EnableEvents = False

line and file is closed before it reaches the

Application.EnableEvents = True

line. Thus upon a user opening the file the macros are off. And for some the

worksheet_change

manages to run all the way threw thus the macros work fine. Does this sound even plausible ? Might adding

Application.Wait (Now + TimeValue("0:00:05"))

after a cell is changed solve the problem ?

 

[VytautasM]

Good day,

An update. Debugged my code found 3 errors, but no Application.EnableEvents missing. Also implemented Joe4 suggested error check. Furthermore checked my own theory and added Application.Wait code. This did help. The first time opening the file no users got an error. But when opening the file a second or third time the same problem occurred and the macros stopped work. But this time a error messaged indicated that a library was missing. To be more precise two libraries. Always the same ones:

After unchecking the missing libraries, macros start working again. As I understand the missing libraries are due to users having different MS Office versions. But still does not answer the question why opening the first time all macros work perfectly. My question would be is there a solution for this apart from installing same MS Office versions for users ?