Excel crashes when renewing auto-filters oncellchange

I use the following code to refresh an autofilter in Excel upon a cell change.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MySheet As Worksheet
  'On Error Resume Next
  'Set MySheet = Application.ActiveSheet
  'MySheet.Columns(1).AutoFilter.ApplyFilter
  'On Error Resume Next
  'Application.EnableEvents = False
  Worksheets("Basisgegevens").Range("$A$1:$A$146").AutoFilter Field:=1, Criteria1:=Array("0", _
        "2", "="), Operator:=xlFilterValues
  'Application.EnableEvents = True
  'On Error GoTo 0
End Sub
开发者_开发百科

I use this to hide rows based on certain criteria. And for that it works really well.

The problem

However when I set validation on a cell and add one of those cool dropdown lists.

To get the cell to look like this:

That part works fine, but as soon as I choose a different value that causes the autofilter to hide/display different cells excel crashes

My theory

The validation drop down changes a cell.

This triggers the VBA code shown above.

However the validation code is still running, whilst the filter settings get reinitialized.

This causes Excel to crash.

How do I fix this?

Will running the VBA event delayed somehow help?

How do I do that?

I don't think there is anything wrong with what you are doing now. But I suspect there is another component causing this crash such as addins. Why don't you unload or delete unnecessary addins ( http://office.microsoft.com/en-us/excel-help/load-or-unload-add-in-programs-HP010096834.aspx) and try the code again?

It looks like a racing condition, if the filter is reapplied before calculation has finished it will make Excel crash.
This does require a large sheet so that calculation takes long enough for this to occur.

Here's the work around:

Option Explicit

Dim ReapplyFilter As Boolean

'OnCalculate is always called twice, before and after calculation.
'I'm only interested in the event after.  
Private Sub Worksheet_Calculate()
  If (Application.CalculationState = xlDone) And (ReapplyFilter = True) Then
    ReapplyFilter = False
    On Error Resume Next
    Worksheets("Basisgegevens").Range("$A$1:$A$146").AutoFilter Field:=1, Criteria1:=Array("0", _
          "2", "3", "4", "5", "="), Operator:=xlFilterValues
    On Error GoTo 0
  End If

End Sub

'Always Reapply the filter on activation of the sheet.
Private Sub Worksheet_Activate()
  Worksheets("Basisgegevens").Range("$A$1:$A$146").AutoFilter Field:=1, Criteria1:=Array("0", _
          "2", "3", "4", "5", "="), Operator:=xlFilterValues
End Sub

'OnChange: set the flag to be picked up by calculation later on.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MySheet As Worksheet
  ReapplyFilter = True
End Sub