Macros stop working an unknown reason.

VytautasM

New Member
Joined
Jan 31, 2020
Messages
26
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do you have
VBA Code:
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

New Member
Joined
Jan 31, 2020
Messages
26
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Do you have
VBA Code:
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:
VBA Code:
Application.EnableEvents = True

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

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

Or:

VBA Code:
Sub Auto_Close()
Application.EnableEvents = True
End Sub

???
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jan 31, 2020
Messages
26
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jan 31, 2020
Messages
26
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
Sub Auto_Close()
or
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 31, 2020
Messages
26
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top