Excel AutoFilter & beforeSave Problem

[derroque]

Hello,

I have the following problem:

I have got a tool that automatically requests data from a database via an ODBC-interface and puts them in an excel template. I also added a macro into that template that is triggered with the beforeSave event and should run after the excel file is filled with the database data.

This works fine except one little thing: the AutoFilter which is part of the macro can not be applied in combination with the beforeSave event (but the rest of the macro works fine). When I run the macro manually (and not with beforeSave), the AutoFilter is applied.

Here is the code for my AutoFilter:

Worksheets(i).Range("$A$3:$A$" & LastRow).AutoFilter Field:=1, Criteria1:="=**|*|**" _
            , Operator:=xlAnd, Criteria2:="<>|*"

I also added this helper to check if the AutoFilter works:

With ActiveSheet
                    If .AutoFilterMode = True And .FilterMode = True Then
                        MsgBox "They are visible and in use"
                    ElseIf .AutoFilterMode = True Then
                        MsgBox "They are visible but not in use"
                    Else
                        MsgBox "They are not visible or in use"
                    End If
End With

But it always returns "They are not visible or in use". I checked if the workbook is active, if the sheet is active, if the sheet is filled with data, if my range is correct, if AutoFilter are enabled, if the sheet is protected and so on but I couldn't find the mistake.

As I said before, when I run the script manually (and not with the beforeSave event) the AutoFilter works fine. I experienced the problem with Excel 2007 & Excel 2013.

I hope anyone can help me.

Best regards,
Daniel

 

[derroque]

*push*