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

[Goose306]

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:

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.

 

[BobUmlas]

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]

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...

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.

 

[Goose306]

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!