CODEWITHSUNDEEP
vbaexcel
Nathaniel Lindsey
Nathaniel Lindsey

Reputation: 135

Excel VBA filter reset works in Immediate window but not in Sub BeforeClose

I've written the following code to reset any validation and/or autofiltering before my workbook closes:

Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next
    Worksheets("Query").Cells.SpecialCells(xlCellTypeAllValidation).Clear

    On Error Resume Next
    Worksheets("Table").Cells.SpecialCells(xlCellTypeAllValidation).Clear

    On Error Resume Next
    Worksheets("Table").ShowAllData

    On Error Resume Next
    Worksheets("Query").Activate

    ThisWorkbook.Save

End Sub

However, 'Worksheets("Table").ShowAllData' only seems to work in the immediate window. When I close the program after filtering the table on sheet "Table" and open it up again, I get

We found a problem with some content in "Book1.xlsm". Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

This didn't happen at first and it seems to have no cause. Why does the filter reset work in the immediate window, but not in my subroutine? How can I fix it?

Upvotes: 0

Views: 363

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149297

No need to have so many OERN On Error Resume Next. Just one will do.

I tried this

Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Worksheets("Query").Cells.SpecialCells(xlCellTypeAllValidation).Clear
    Worksheets("Table").Cells.SpecialCells(xlCellTypeAllValidation).Clear
    On Error GoTo 0

    If Worksheets("Table").FilterMode Then Worksheets("Table").ShowAllData

    Worksheets("Query").Activate

    ThisWorkbook.Save
    DoEvents
End Sub

and I did not get any errors. Try it.

We found a problem with some content in "Book1.xlsm". Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

If you still get an error then it is not because of this piece of code. You need to see the report as to what was repaired. I have a feeling one of your DataValidation list is causing this problem. Like I said, check the repair report. If the report says that the Datavalidation was repaired then you need to check that.

Upvotes: 0

user8127890
user8127890

Reputation:

never use On Error Resume Next or you will never learn anything

you need to check to see if Autofilter is on first because it's a toggle...

  If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
  End If

or

  If ActiveSheet.FilterMode Then
    ActiveSheet.AutoFilterMode = False
  End If

Upvotes: 0

Related Questions