Prevent leaving worksheet if in filter mode

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I need to make sure that if data on a sheet is filtered, that the user must
reset the filter back to "all" before he can close the workbook.
The code I have (in the sheet's module) to reset the filter/s is:
Sub RemoveFilter()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End Sub
I expect a before close event needs to happen in ThisWorkbook but I don't
know how to construct the code.

Rob
 
Hi Rob,

You could try to put it in the Private BeforeClose event:

Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End Sub

but if user hasn't saved the workbook, then the ShowAll "mode" is not on,
when the workbook is reopened. Other possibility is to put it in the
BeforeSave event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End Sub

but this Shows All every time the workbook is saved. Put these codes in
the ThisWorkbook object.

I hope these help a little.

- Asser
 
In a large Excel file, leaving the filter on when saving
may cause significant File-Open times.

I feel that this behing your question.

I had a file with 60,000 records. Removing filetrs
before saving would lead to 5-8 second re-Open times.

Where as saving the same file with multiple filters ON
did cause 20 minute Open times!

I also believe that many who believe that Excel ahs
locked up when in relality Excel is just attempting to
compute the openin filted screen
 
Rob

use a before close event procedure

Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _
Cancel as Boolean)
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

End Sub
 
Hi Dennis, thanks for your response. In actual fact, it's the opposite that
I want to do. I definately want to make sure that all filters are removed
before saving.
Rob
 
Thankyou Peter and Asser. I think I'll probably go with the before save
event that Asser suggested as it seems safer to me .
Rob
 
Just a suggestion.

Instead of using workbook_beforeclose, use workbook_open. Then you don't have
to worrying about saving the file (maybe the user made a disastrous mistake and
really doesn't want to save it.)
 
Not sure why that would be better, Dave. If the user's not saving it,
everything reverts back with filters off, because when the user does save
it, the before close event is run turning the filters off.
Rob
 
If the user filters, does an intermediate save with the data filtered, then
makes that drastic error--do you unfilter and save? (Might make someone mad!)

I just figure it's safer to do all the setup work right when you open the
workbook.
 
Yes, that makes a lot of sense, Dave. I guess I was a little reluctant as I
thought I read somewhere (can't remember where), that saving files while
filter is on can, in certain circumstances, create a problem. Can't
remember the problem or who said it, but if that's not true, then Before
Save is what I'll do.
Thanks for the advice,
Rob
 
Back again. Gee it frustrates me when something simple doesn't work. I
don't think I'm ever going to get a grasp of VBA!
I put this in the Workbook_Open as you suggested, BUT....

If Sheet4.FilterMode Then
Sheet4.ShowAllData
End If

....doesn't work

Also tried...

With Sheet4
.Activate
.ShowAllData
.EnableAutoFilter = True
.Protect password:="", UserInterfaceOnly:=True
.Range("H1525").End(xlUp)(2, -1).Select
End With

.....to no avail.

Grrrr!

Do I need to stick more stuff in the Private Sub Workbook_Open() line?
Also, can you explain why....

If Sheet4.FilterMode Then
Sheet4.ShowAllData

needs an End If and ....

If Sheet4.FilterMode Then Sheet4.ShowAllData

doesn't need an End If?
Maybe I should just buy a book but I fear that VBA for Dummies may be over
my head :(
What am I missing here (apart from a vital section in my brain).
Help please,
Rob

rob nobel said:
Yes, that makes a lot of sense, Dave. I guess I was a little reluctant as I
thought I read somewhere (can't remember where), that saving files while
filter is on can, in certain circumstances, create a problem. Can't
remember the problem or who said it, but if that's not true, then Before
Save is what I'll do.
Thanks for the advice,
Rob


Dave Peterson said:
If the user filters, does an intermediate save with the data filtered, then
makes that drastic error--do you unfilter and save? (Might make someone mad!)

I just figure it's safer to do all the setup work right when you open the
workbook.
Click to expand...
before
Click to expand...
 
If your sheet isn't filtered, then .showalldata will fail.

If you've applied the filter, but no filter is active, then .showalldata will
fail.

You could avoid the error by just accepting that you want to ignore the error if
it happens:

on error resume next
sheet4.showalldata
on error goto 0

or you could check to see if everything is hunky-dorey:

Option Explicit

Private Sub Workbook_Open()

With Sheet4
.Select
.EnableAutoFilter = True
.Protect Password:="", UserInterfaceOnly:=True
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
.Range("H" & .Rows.Count).End(xlUp)(2, -1).Select
End With

End Sub

Notice that I moved the protection higher in the procedure--so that the
protected worksheet can have the code work against it.

======
there are two styles of If/Then statements.

One is one (logical) line and the other is the block if/then style.

If you put it all on one logical line:

if a = b then msgbox "hi there"
or equivalently:
if a = b then _
msgbox "hi there"

This is the first style.

If you do:
if a = b then
Msgbox "hi there"
end if
it's block style.

I usually (but not always) like the block style. But it's mostly a matter of
preference (you or your boss's!).

I find that when I'm scanning code, it's easier to line up if/else/end if's.

(and when I decide that I want to do 45 things between the Then and end if, it's
easier to insert those lines, too.)



rob said:
Back again. Gee it frustrates me when something simple doesn't work. I
don't think I'm ever going to get a grasp of VBA!
I put this in the Workbook_Open as you suggested, BUT....

If Sheet4.FilterMode Then
Sheet4.ShowAllData
End If

...doesn't work

Also tried...

With Sheet4
.Activate
.ShowAllData
.EnableAutoFilter = True
.Protect password:="", UserInterfaceOnly:=True
.Range("H1525").End(xlUp)(2, -1).Select
End With

....to no avail.

Grrrr!

Do I need to stick more stuff in the Private Sub Workbook_Open() line?
Also, can you explain why....

If Sheet4.FilterMode Then
Sheet4.ShowAllData

needs an End If and ....

If Sheet4.FilterMode Then Sheet4.ShowAllData

doesn't need an End If?
Maybe I should just buy a book but I fear that VBA for Dummies may be over
my head :(
What am I missing here (apart from a vital section in my brain).
Help please,
Rob
Click to expand...
 
I've seen posts that describe problems when opening giant worksheets with
filters enabled. Sometimes, it can slow down recalculations.

But I don't think I've ever really experienced that in real life.

rob said:
Yes, that makes a lot of sense, Dave. I guess I was a little reluctant as I
thought I read somewhere (can't remember where), that saving files while
filter is on can, in certain circumstances, create a problem. Can't
remember the problem or who said it, but if that's not true, then Before
Save is what I'll do.
Thanks for the advice,
Rob
Click to expand...
 
Something to keep in mind if my worksheet turns into a giant.

Thanks for the now working procedure. I decided to go with including it in
the With Sheet4 procedure rather than ignoring the error.
Can't quite understand your comment...
"If you've applied the filter, but no filter is active" ...so still can't
see why
If Sheet4.FilterMode Then
Sheet4.ShowAllData
End If
didn't work, because I thought that would check if filter was on or off??

Thanks for the explanation regarding the End if, etc.

Rob
 
You're right (again!).

My guess is your code blew up because the worksheet wasn't protected with
userinterfaceonly:=true, yet.
 
userinterfaceonly means that you want the human users stopped from doing stuff.
But you want to allow your code to do stuff beyond the means of mortal men!

But if your macro tries to do stuff before excel knows that it should allow it,
kablewie!

So just tell excel first (by moving it up in the code), then your code can
continue.
 
Hmmm, starting to get the picture I think.
But, (bet you hate that word),
Problem 1. As it was part of
.Protect Password:="", UserInterfaceOnly:=True
I can't quite see why it's important where that is placed, because if I
didn't have the .Protect.... line in at all, I would never even have the
UserInterfaceOnly:=True in the code at all.

Problem 2. How do you pronounce "kablewie!"

Rob
 
Excel remembers that the worksheet was protected after you close the workbook
and reopen it.

But it doesn't remember that you want to do special stuff (userinterfaceonly)
with it. (I guessed that your worksheet was protected when you opened it (from
before).

ka-blew-eeeee

The sounds of explosions!

If you turn on "provide feedback with animation", you might hear excel scream!
(or maybe not).


rob said:
Hmmm, starting to get the picture I think.
But, (bet you hate that word),
Problem 1. As it was part of
.Protect Password:="", UserInterfaceOnly:=True
I can't quite see why it's important where that is placed, because if I
didn't have the .Protect.... line in at all, I would never even have the
UserInterfaceOnly:=True in the code at all.

Problem 2. How do you pronounce "kablewie!"

Rob
Click to expand...
 
Chuckle and thanks!

Rob

Dave Peterson said:
Excel remembers that the worksheet was protected after you close the workbook
and reopen it.

But it doesn't remember that you want to do special stuff (userinterfaceonly)
with it. (I guessed that your worksheet was protected when you opened it (from
before).

ka-blew-eeeee

The sounds of explosions!

If you turn on "provide feedback with animation", you might hear excel scream!
(or maybe not).
Click to expand...
 
Back
Top