Solved

How to remove filters on exit

Posted on 2011-03-23
55
386 Views
Last Modified: 2012-05-11
Dear experts,

My worksheet contains some filters in some fields. Is there a way to remove these fields from a VB code on exit (when the workbook is closed) ?

Thanks

currentdb
0
Comment
Question by:currentdb
  • 23
  • 23
  • 5
  • +1
55 Comments
 
LVL 20

Expert Comment

by:pari123
ID: 35199433
Hi,
You can use the following code to remove the autofilter on exit.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveSheet.ShowAllData
    Selection.AutoFilter
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199443
Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '~~> Check for filter, turn off if exists
    If ActiveSheet.AutoFilterMode Then
        Sheets("Sheet1").AutoFilter
    End If
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199450
crossover.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199463
To remove autofilter from every sheet in the workbook, do this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        '~~> Check for filter, turn off if exists
        If ws.AutoFilterMode Then ws.AutoFilter
    Next
End Sub

Open in new window


Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199482
one worksheet is enough for now, but I'll remember your code if there are several filter on more than one worksheet. Lemme try both codes, yours and the one from pari123.
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199516
I'm back. Tried both, but no effect. I closed the workbook and when I reopened it, the filter was still there.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'~~> Check for filter, turn off if exists
    If ActiveSheet.AutoFilterMode Then
        Sheets("QUERY_FOR_GSL2").AutoFilter
    End If
End Sub

Open in new window


Maybe the code needs to be after the one that is formatting the AY column ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199532
Did you save it before closing ?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199542
Now I have the first compile error. It was not there when I re-opened the workbook. But now as I wanted to close it again, a 438 copile error appeared ''Property or method not generated by this object''
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199546
Did you save it before closing ? --> Yes I did

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199549
May I see the workbook?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199551
It just highlights the line 4
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199562
Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'~~> Check for filter, turn off if exists
    If Sheets("QUERY_FOR_GSL2").AutoFilterMode Then
        Sheets("QUERY_FOR_GSL2").AutoFilter
    End If
End Sub

Open in new window


Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35199564
Surely:
Private Sub Workbook_BeforeClose(Cancel As Boolean)  
'~~> Check for filter, turn off if exists  
    If Sheets("QUERY_FOR_GSL2").AutoFilterMode Then  
        Sheets("QUERY_FOR_GSL2").ShowAllData
    End If  
End Sub

Open in new window

0
 
LVL 20

Expert Comment

by:pari123
ID: 35199571
Can you tell me where did you paste the code?  you need to save it on the Thisworkbook page and not in a module.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveSheet.ShowAllData
    Selection.AutoFilter
    ThisWorkbook.Save
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199585
If I am not wrong,

.ShowAllData

doesn't turn off the autofilter. It just shows all data :)

Sid
0
 
LVL 20

Expert Comment

by:pari123
ID: 35199588
Hi,
Can you also mention what is your version of Excel?
0
 
LVL 20

Expert Comment

by:pari123
ID: 35199603
==> If I am not wrong,

.ShowAllData

doesn't turn off the autofilter. It just shows all data :)


you are right...
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199604
Can you tell me where did you paste the code?  you need to save it on the Thisworkbook page and not in a module. --> It was saved in ThisWorkbook page. Saving it into a module would mean execute it manually...

Sid, I'm going to try the code you suggested at post ID 35199562 before posting a sample of the workbook.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199631
>>>Sid, I'm going to try the code you suggested at post ID 35199562 before posting a sample of the workbook.

and save the workbook manually.

A Suggestion: Do not include

ThisWorkbook.Save

in the before close event

as it would save every time you close the workbook even when you don't want to save the changes.

Sid
0
 
LVL 20

Expert Comment

by:pari123
ID: 35199646
Can you also try the following once too -


d
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveSheet.ShowAllData
    Selection.AutoFilter
    ThisWorkbook.Save
End Sub

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35199656
It was not clear whether the asker wanted the filter completely removed, or just all data visible.

Yours won't work since the Autofilter property of the worksheet simply returns an Autofilter object.

You could use:
Private Sub Workbook_BeforeClose(Cancel As Boolean)    
'~~> Check for filter, turn off if exists    
    If Sheets("QUERY_FOR_GSL2").AutoFilterMode Then    
        Sheets("QUERY_FOR_GSL2").AutoFilterMode = False
    End If    
End Sub

Open in new window


if you actually want to remove the filter completely.
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199679
pari: Can you also mention what is your version of Excel? --> Excel 2007
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 195 total points
ID: 35199704
If it's a table on the sheet then something like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)      
'~~> Check for filter, turn off if exists      
    Sheets("QUERY_FOR_GSL2").Listobjects(1).Autofilter.ShowAllData
End Sub

Open in new window

to simply show all the data, or:
Private Sub Workbook_BeforeClose(Cancel As Boolean)      
'~~> Check for filter, turn off if exists      
    Sheets("QUERY_FOR_GSL2").Listobjects(1).ShowAutofilter
End Sub

Open in new window


to remove it.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199723
I just realised what Rory said is correct however it still won't take off the autofilter.

This is tried and tested.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '~~> Check for filter, turn off if exists
    If Sheets("QUERY_FOR_GSL2").AutoFilterMode Then
        Sheets("QUERY_FOR_GSL2").AutoFilterMode = False
        ret = MsgBox("Do you want to save this workbook", vbOKCancel)
        If ret = vbOK Then ThisWorkbook.Save
    End If
End Sub

Open in new window


Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199765
rorya: your code seems to work, but before inserting it, my computer popped me with these messages ''Not enough memory''. Weird. And I don't even have many opened apps, just Excel and the workbook is very light.

The other problem that appeared is that now it asks me to save the workbook even if I already clicked on the ''Save'' button at the top.

Sid just suggested that I don't have to include the ThisWorkbook.Save line. It's not included, but then why Excel asks me to save the workbook for a second time if I already saved it ?

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199771
See the code that I posted :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199814
Sid,

You have not defined the ''ret'' variable as it returns a compile error.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199825
Sorry, Add the at the top of the code

Dim Ret

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199846
The workbook just crashed for a nineth time in a row. Seems like something in the code eats lots of system memory. Maybe that causes the ''Insufficient memory'' message to appear.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35199857
If you save before closing, then the closing code changes the filters, so you need to save that change too. Sid's code for the Save part takes care of that.
I can't really help with errors that occurred before you added my code. ;)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199860
Can u upload the file?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199905
rorya: I can't really help with errors that occurred before you added my code. --> These errors were not caused by your code, not even by Sid's code, but I have no real idea. Maybe this computer just play devil games with me :)

sid: here's the file
userform-sample-v7b-5.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199934
It works perfectly for me.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199939
Restart your pc and then try :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35199955
Sid, yes it works, but is that necessary to click 2 times when you want to save ? First Excel itself asks you to save, and after, it's the message box from the Private Sub Workbook_BeforeClose event. Does that mean there is no possiblity just to use one confirmation message instead of two ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35199974
Hmmm Strange

I get only one mesage box.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200000
I tested the code back on the other sheet that is linked to a Ms-Access table, but I'm puzzled that the code does not have any effect.

What I can notice about this sheet that is linked this way is that all rows are already using a filter by default. Tried to shut it off manually, but it does not work.
Now the columns that the filter are columns O and AY. And that means a double filter...first filter from Access then second from Excel. If removing the Excel filter, data will be as it is, not filtered even if the Access filter is still on each column.

I don't know if there's some solution for this or if it has to be done manually every time.
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200023
Hmmm Strange
I get only one mesage box.  --> If you don't click at all on this small disk at the top of Excel, just close, really close the worksheet, then first the Excel system message appears...you click ''yes''...and second the message box, but by the time you click ''yes'', the filter was already removed.  

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35200044
Nope. I still get only 1 message box.

Also the file uploaded, is that the original file? I mean I don't see any connections to Access?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200066
Also the file uploaded, is that the original file? I mean I don't see any connections to Access? --> It's just a sample file as the original file is much bigger in size. But if you have Access, I can post you a sample Access mdb with the Excel file linked to the Access table.

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35200093
Ah! I think the Access is making a change again in the closing event. Ok do this small exorcise.

Replace this line in the code

ret = MsgBox("Do you want to save this workbook", vbOKCancel)

with this

ret = MsgBox("Hello World", vbOKCancel)

Now close the workbook. Which message do you see first?

1) "Hello World" or

2) "Do you want to save the changes you made to ..."

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200172
Replaced the line as you suggested. Now the message I see first is #2 "Do you want to save the changes you made to ..."

The #1 Hello World does not appear at all.

It's better to re-upload the same sheet, but now with the sheet that is linked to Access (even if you don't have Access).
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35200220
Yes that would help.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200311
Here's a sample.

There are 2 QUERY_FOR_QSL2 sheets.
The one that has a ''x'' mark at the end is old. The one without the ''x'' at the end works.

I tested both. The old one works, but of course, the 2 message box continue to appear. The other one (with blue lines) is from Access. As I worked to reduce it in size, data disappears once I'm trying to filter by acctopid 13. Nothing has changed as everything is still as it was before.
userform-sample-v7b-5.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35200438
Ok Few things. This

Sub Sample()
Msgbox Sheets("QUERY_FOR_GSL2").AutoFilterMode
End Sub

gives me false even if the autofilter is ON. Seems like it doesn't recognize that as autofilter and that is the reason why you didn't get the "Hello World" message ;)

I could manually remove the autofilter by first clicking on Cell A1 and then removing the autofilter.

I am experimenting more. Will get back to you shortly.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200442
I managed to make it work, I mean data from the Access table that was not filtered. I was again the AY column, so I had to re-format it.

As for the filter, still trying to see what does not work.

I re-posted the sample.
userform-sample-v7b-5.xlsm
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200458
No problem, take your time. We just posted at the same time. Hope you had time to read my post ID 35200442 :)
0
 
LVL 1

Author Comment

by:currentdb
ID: 35200519
I went in the Access table and the autofilter seems to be off, but looks on if I click on any column...weird.

I posted a sample Access db if you need to look furthermore.

Thanks.
master-file.mdb
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35200799
Ok. I have reset the connection to the database in the Excel file that I have. And I don't get the Message box that I have on the Workbook close event as it doesn't recognize the "Autofilter" as autofilter in Sheet "QUERY_FOR_GSL2". Investigating further I realized that the autofilter in a table behaves differently.

Try this code now :)

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Ret
    If Sheets("QUERY_FOR_GSL2").ListObjects("Tableau_master_file_1").ShowAutoFilter Then
        Sheets("QUERY_FOR_GSL2").ListObjects("Tableau_master_file_1").ShowAutoFilter = False
        Ret = MsgBox("Do you want to save this workbook", vbOKCancel)
        If Ret = vbOK Then ThisWorkbook.Save
    End If
End Sub

Open in new window


0
 
LVL 1

Author Comment

by:currentdb
ID: 35200900
Looks like I have another compile error (Error 9: this does not belong to the selection) and it highlights line 5.

I think it makes reference at Tableau_master_file_1..but where you do see it ? Would it not be ''master_file.mdb'' which it leads to the Access database ?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 305 total points
ID: 35200943
Oh you uploaded a fresh file... in ID: 35200442. This file has a different table.

Try this code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Ret
    If Sheets("QUERY_FOR_GSL2").ListObjects("Tableau_master_file").ShowAutoFilter Then
        Sheets("QUERY_FOR_GSL2").ListObjects("Tableau_master_file").ShowAutoFilter = False
        Ret = MsgBox("Do you want to save this workbook", vbOKCancel)
        If Ret = vbOK Then ThisWorkbook.Save
    End If
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35200960
>>>I think it makes reference at Tableau_master_file_1..but where you do see it ?

To see the table name paste this code in a module and then run it :)

Sub FindTablesName()
    Dim ws As Worksheet
    Dim objList As ListObject
    Set ws = Sheets("QUERY_FOR_GSL2")
    For Each objList In ws.ListObjects
        MsgBox objList.Name
    Next
End Sub

Open in new window


Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35201154
Wow! That's very surprising!

And you are right, it works! The filter is removed for good :) Thanks!!! :)

BTW, the code to find the table name is very good. I'll remember it for future use.

Now I'll award points, you'll get most of them and rorya will get the other part as it was also part of his code. Let me know if it's ok for you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35201165
Definitely it is ok with me :)

Sid
0
 
LVL 1

Author Closing Comment

by:currentdb
ID: 35201230
Thank you very much for your help on this one as it was not really an easy one. I hope it didn't make you drink a lot of coffee :)
Thanks again! :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now