Excel AutoFilter & beforeSave Problem

derroque

New Member
Joined
May 7, 2013
Messages
2
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:

Code:
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:

Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top