Is there a way to stop this VBA from falling over if an error happens?

Goose306

Board Regular
Joined
Sep 26, 2014
Messages
52
Hello;

I have some embedded VBA I'm using that clears the contents of a cell if another cell is changed. These columns are used for data trending, and so its basically to ensure the data is trending correctly (i.e. sub-area matches area)

Here is the code:

Code:
Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Cells.Count > 1 Then Exit Sub
     
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        ActiveCell.Offset(0, 1).ClearContents
    
    End If
      
      If Target.Cells.Count > 1 Then Exit Sub
     
    If Not Intersect(Target, Range("O:O")) Is Nothing Then
        ActiveCell.Offset(0, 1).ClearContents
    
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
     
    If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
        ActiveCell.Offset(0, 1).ClearContents
    
    End If
     
End Sub

The issue I have is this code is embedded in a large excel file that is shared between multiple (up to 10) users, all working and editing off the same or different sheets at the same time. I know sharing causes its own host of issues, and every now and then the code "falls over" and the ClearContents command fails with a run-time error with some crazy number and the text "Method 'ClearContents' of object 'Range' failed"

This is a big pain because when it happens the macro gets stuck in a loop. Because the file is shared, VBA is not accessible to break, meaning the user has to force out of excel.

My question is two-pronged:
Is there anything that can actually stop this error from happening? I believe its related to the funkiness of shared files and having multiple changes happen back to back from different people (I can only make it "fall over" by myself if making a lot of changes very fast).

If I can't do anything to help this error, is there something I can do so it skips over the error so users don't have to force out of the file? Its fine if the VBA doesn't run that one time and the cell doesn't clear. I'll take 99% accuracy over users being aggravated having to force out of excel.
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
I suggest your first statement inside this procedure should be:
Application.EnableEvents = False (well, after the first If statement! - after the possibility of the Exit Sub)
and just before the End Sub:
Application.EnableEvents = True

When your currend sub clearscontents, that re-invokes the Worksheet_Change event and will cause loops galore!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Goose306,

Sharing issues apart, here are my thoughts...

The second and third ....Exit Sub lines are redundant.
When a ClearContents line is actioned it fires the WorksheetChange code again so your code runs twice as often as it should which increases any chance of sharing conflict.

The 'ActiveCell' is whatever cell is selected or Tabs to or Returns to after the change within the Target cell.

Might it be better to reference the cell to be cleared relative to the Target cell?

Ignoring that final point, you might wish to try the following...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Cells.Count > 1 Then Exit Sub  
    If Not (Target.Column = 13 Or Target.Column = 15 Or Target.Column = 17) Then Exit Sub


        Application.EnableEvents = False   'Disable events
        ActiveCell.Offset(0, 1).ClearContents   'Active cell????
        Application.EnableEvents = True   'Enable events
     
End Sub

Hope that helps.
 
Last edited:

Goose306

Board Regular
Joined
Sep 26, 2014
Messages
52
Excellent advice from both of you!

I knew I had read about using enable events somewhere, but it somehow got lost in my mind...

I've integrated your suggestions and tried to break it myself again and was unsuccessful. I'll send it out to some co-workers and have them try their best at it.

Thanks again for all you're help, I'll follow-up if I need any further help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

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