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
精彩评论