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.

 

[MARK858]

Do you have

Application.EnableEvents = False

anywhere in your code? if you do and either the code errors or you exit the code for any other reason before you set it to True then that will stop workbook_open and worksheet_change macros from running.

 

[VytautasM]

Do you have

Application.EnableEvents = False

anywhere in your code? if you do and either the code errors or you exit the code for any other reason before you set it to True then that will stop workbook_open and worksheet_change macros from running.

Mark,

Have considered this. Yes I do have this, but I always end it with:

Application.EnableEvents = True

To rule this option out a 100% would it be a good idea to add this line of code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = True
End Sub

Or:

Sub Auto_Close()
Application.EnableEvents = True
End Sub

???

 

[MARK858]

It is an option as a workaround, but if that is what is causing the issue it is much better to work out out why your original code isn't reaching the Application.EnableEvents = True line all the time.

 

[VytautasM]

It is an option as a workaround, but if that is what is causing the issue it is much better to work out out why your original code isn't reaching the Application.EnableEvents = True line all the time.

Will try, checking the code again. I have a beefy worksheet_change macro, were I have Application.EnableEvents = False/Application.EnableEvents = True in between every IF loop, would it be enough to have just one Application.EnableEvents = False at the start and Application.EnableEvents = True at the end ? In other words does the whole worksheet_change macro run upon a change in the sheet or just a partial code when a IF statement is true ?

 

[MARK858]

In General the whole code runs but obviously it depends how you have coded it, if you have lines like Exit Sub in the code then it ends there.
You would most of the time have the EnableEvents at the start and finish but we can't tell much without seeing the code.

 

[VytautasM]

In General the whole code runs but obviously it depends how you have coded it, if you have lines like Exit Sub in the code then it ends there.
You would most of the time have the EnableEvents at the start and finish but we can't tell much without seeing the code.

No Exit Sub lines. But since you mention it maybe should add some to make the workbook faster. Will play around. Also which would be better

Sub Auto_Close()

or

Private Sub Workbook_BeforeClose(Cancel As Boolean)

?

 

[MARK858]

Personally BeforeClose but beware if you are going to put in Exit Sub's as then you will have to enableevents each time and it is still unlikely to solve your issue.

 

[Joe4]

Also be aware of any abnormal exits/errors that kick you out of the code before getting to the line where events are re-enabled.
One way to avoid this is to add error handling code to all of your procedures.

 

[VytautasM]

Also be aware of any abnormal exits/errors that kick you out of the code before getting to the line where events are re-enabled.
One way to avoid this is to add error handling code to all of your procedures.

Hey Joe,

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