Auto Filter Macro

[Snakeoids]

Greetings,
I have created a button on a worksheet with the following code:
Sub Macro4()
ActiveSheet.ShowAllData
End Sub

When I have the Autofilter active for a column(s), I just press this button
and the filter is taken off. No problems. However if I press the button
when the Autofilter is not filtering any of the columns, I get a debug error.
How do I tell the computer that if I press the button while the Autofilter
is already off, don't do anything? Thanks.

[StinkeyPete]

Try this:

Sub Macro4()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub


"Snakeoids" wrote:

> Greetings,
> I have created a button on a worksheet with the following code:
> Sub Macro4()
> ActiveSheet.ShowAllData
> End Sub
>
> When I have the Autofilter active for a column(s), I just press this button
> and the filter is taken off. No problems. However if I press the button
> when the Autofilter is not filtering any of the columns, I get a debug error.
> How do I tell the computer that if I press the button while the Autofilter
> is already off, don't do anything? Thanks.

[Dave Peterson]

Another way is to just check first:

With Activesheet
If .FilterMode Then
.ShowAllData
End If
End With

Snakeoids wrote:
>
> Greetings,
> I have created a button on a worksheet with the following code:
> Sub Macro4()
> ActiveSheet.ShowAllData
> End Sub
>
> When I have the Autofilter active for a column(s), I just press this button
> and the filter is taken off. No problems. However if I press the button
> when the Autofilter is not filtering any of the columns, I get a debug error.
> How do I tell the computer that if I press the button while the Autofilter
> is already off, don't do anything? Thanks.

--

Dave Peterson

[BizMark]

Indeed. On Error Resume (Next) can be a nasty catch all.

Also - beware of using

On Error Resume (Next)
and
On Error Goto 0

in the same sub-routine (where you might want to ignore some errors but
stop on others) - if an error is trapped by the former, an error
occuring during the latter can cause an Illegal Operation error in
Excel 97 (and possibly Excel 2000 - I think this was fixed in a later
version).

BizMark


--
BizMark

[Dave Peterson]

I'm not sure what error you're writing about (in xl97), but I agree that you
should use that "on error resume next" sparingly. And turn it back to your
error handler (or goto 0) right after the code that may cause the error.



BizMark wrote:
>
> Indeed. On Error Resume (Next) can be a nasty catch all.
>
> Also - beware of using
>
> On Error Resume (Next)
> and
> On Error Goto 0
>
> in the same sub-routine (where you might want to ignore some errors but
> stop on others) - if an error is trapped by the former, an error
> occuring during the latter can cause an Illegal Operation error in
> Excel 97 (and possibly Excel 2000 - I think this was fixed in a later
> version).
>
> BizMark
>
> --
> BizMark

--

Dave Peterson

[StinkeyPete]

I didn't realize that if I didn't offer a disclaimer on using "On Error
Resume Next" that it would be such an issue. So here it is: "On Error
Resume Next" is the quick and dirty solution to your problem. Do not use
this if you are performing brain surgery with excel. Otherwise it will work
just fine.

"Snakeoids" wrote:

> Greetings,
> I have created a button on a worksheet with the following code:
> Sub Macro4()
> ActiveSheet.ShowAllData
> End Sub
>
> When I have the Autofilter active for a column(s), I just press this button
> and the filter is taken off. No problems. However if I press the button
> when the Autofilter is not filtering any of the columns, I get a debug error.
> How do I tell the computer that if I press the button while the Autofilter
> is already off, don't do anything? Thanks.

[BizMark]

StinkeyPete,

What I made was a valid point that people would do well to heed. That
is what this forum is about.

If you can't handle considered advice from other people with experience
which may supplement yours, I suggest you hang on to your personal
and/or sarcastic comments. They are not needed, not helpful and not
civil.

BizMark


--
BizMark

[BizMark]

Dave Peterson Wrote:
> I'm not sure what error you're writing about (in xl97),


I think millions of Excel users around the world are only too aware
what error message this is.

Surely you can't tell me you've NEVER had this?

"Microsoft Excel has performed an Illegal Operation and will be shut
down. If the problem persists, please contact the software vendor".

Or, in later versions,

"Microsoft Excel has encountered a problem and needs to close. We
apologise for any inconvenience. Do you want to submit an error
report?"

There are a myriad causes for this behaviour, some are consistently
repeatable and therefore trappable, and some are not. Any situation
which causes these to happen with any regularity, it is useful to
people to point out as it can prevent them from losing many man hours
or days of work. Some are:

1. Using On Error Resume Next then On Error Goto 0
2. Editing VBA code when the 'Forms' toolbar was open on a Dialogsheet
before switching to the VBE, then saving in the VBE
3. Operating on range names in macros when the name definitions contain
#REFs etc.
4. One user running macro in a workbook, another user having the same
workbook open as Read-Only, then the other user does 'Save As' to make
a copy - result - Illegal Operation/Encountered a Problem error occurs
for user running the macros.

There are many others that don't spring to mind straight away.
Sometimes this error cannot be explained.

However, I think StinkeyPete needs to realise that it needn't be an
issue just because somebody has issued a warning over his solution.

BizMark


--
BizMark

[Dave Peterson]

I've gotten that error (one or two times <vbg>). But none that I could
attribute to an "on error resume next" line.

#2. I use xl2003 and don't use many dialog sheets, but I almost always have the
forms toolbar, drawing toolbar, and control toolbox toolbar visible. As a test,
I added a dialog sheet and then swapped to the VBE. Nothing bad happened.

#3. I don't recall ever having excel crash when I had range names that had
errors. (My code crashed--but not excel.

#4. I've never seen excel crash because of this, either.

I've found that excel crashes with I do something wrong with an API function or
maybe even a corrupt workbook. Or other things that I couldn't pinpoint
(non-reproducible types).




BizMark wrote:
>
> Dave Peterson Wrote:
> > I'm not sure what error you're writing about (in xl97),
>
> I think millions of Excel users around the world are only too aware
> what error message this is.
>
> Surely you can't tell me you've NEVER had this?
>
> "Microsoft Excel has performed an Illegal Operation and will be shut
> down. If the problem persists, please contact the software vendor".
>
> Or, in later versions,
>
> "Microsoft Excel has encountered a problem and needs to close. We
> apologise for any inconvenience. Do you want to submit an error
> report?"
>
> There are a myriad causes for this behaviour, some are consistently
> repeatable and therefore trappable, and some are not. Any situation
> which causes these to happen with any regularity, it is useful to
> people to point out as it can prevent them from losing many man hours
> or days of work. Some are:
>
> 1. Using On Error Resume Next then On Error Goto 0
> 2. Editing VBA code when the 'Forms' toolbar was open on a Dialogsheet
> before switching to the VBE, then saving in the VBE
> 3. Operating on range names in macros when the name definitions contain
> #REFs etc.
> 4. One user running macro in a workbook, another user having the same
> workbook open as Read-Only, then the other user does 'Save As' to make
> a copy - result - Illegal Operation/Encountered a Problem error occurs
> for user running the macros.
>
> There are many others that don't spring to mind straight away.
> Sometimes this error cannot be explained.
>
> However, I think StinkeyPete needs to realise that it needn't be an
> issue just because somebody has issued a warning over his solution.
>
> BizMark
>
> --
> BizMark

--

Dave Peterson

[BizMark]

Many of these problems seem to be less prevalent with later versions
(the above conditions caused them repeatedly with xl97) - conditions 1
and 2 have caused me problems here at work on xl2003.

There is a bug which leavs the 'Forms' toolbar floating when switching
from worksheet view to VBE view, and saving when this happens
invariably corrupts the workbook. Many times when I've tried it with
later versions as I've progressed through the versions, I've expected
this problem to be resolved, but the same thing has happened time and
time again, regardless. And this is on different types of PC, using
different types of network software (Novell and NT Server, various
versions) and at home with no network.

BizMark

Dave Peterson Wrote:
> I've gotten that error (one or two times vbg). But none that I could
> attribute to an "on error resume next" line.
>
> #2. I use xl2003 and don't use many dialog sheets, but I almost always
> have the
> forms toolbar, drawing toolbar, and control toolbox toolbar visible.
> As a test,
> I added a dialog sheet and then swapped to the VBE. Nothing bad
> happened.
>
> #3. I don't recall ever having excel crash when I had range names that
> had
> errors. (My code crashed--but not excel.
>
> #4. I've never seen excel crash because of this, either.
>
> I've found that excel crashes with I do something wrong with an API
> function or
> maybe even a corrupt workbook. Or other things that I couldn't
> pinpoint
> (non-reproducible types).
>
>
>
>
> BizMark wrote:-
>
> Dave Peterson Wrote:-
> I'm not sure what error you're writing about (in xl97),-
>
> I think millions of Excel users around the world are only too aware
> what error message this is.
>
> Surely you can't tell me you've NEVER had this?
>
> "Microsoft Excel has performed an Illegal Operation and will be shut
> down. If the problem persists, please contact the software vendor".
>
> Or, in later versions,
>
> "Microsoft Excel has encountered a problem and needs to close. We
> apologise for any inconvenience. Do you want to submit an error
> report?"
>
> There are a myriad causes for this behaviour, some are consistently
> repeatable and therefore trappable, and some are not. Any situation
> which causes these to happen with any regularity, it is useful to
> people to point out as it can prevent them from losing many man hours
> or days of work. Some a
>
> 1. Using On Error Resume Next then On Error Goto 0
> 2. Editing VBA code when the 'Forms' toolbar was open on a
> Dialogsheet
> before switching to the VBE, then saving in the VBE
> 3. Operating on range names in macros when the name definitions
> contain
> #REFs etc.
> 4. One user running macro in a workbook, another user having the same
> workbook open as Read-Only, then the other user does 'Save As' to
> make
> a copy - result - Illegal Operation/Encountered a Problem error
> occurs
> for user running the macros.
>
> There are many others that don't spring to mind straight away.
> Sometimes this error cannot be explained.
>
> However, I think StinkeyPete needs to realise that it needn't be an
> issue just because somebody has issued a warning over his solution.
>
> BizMark
>
> --
> BizMark-
>
> --
>
> Dave Peterson


--
BizMark

[Dave Peterson]

When I was running xl97, I'd have those toolbars showing, too. I don't recall
any problems with corrupted workbooks. But that's just my experience. Your
mileage may vary.

BizMark wrote:
>
> Many of these problems seem to be less prevalent with later versions
> (the above conditions caused them repeatedly with xl97) - conditions 1
> and 2 have caused me problems here at work on xl2003.
>
> There is a bug which leavs the 'Forms' toolbar floating when switching
> from worksheet view to VBE view, and saving when this happens
> invariably corrupts the workbook. Many times when I've tried it with
> later versions as I've progressed through the versions, I've expected
> this problem to be resolved, but the same thing has happened time and
> time again, regardless. And this is on different types of PC, using
> different types of network software (Novell and NT Server, various
> versions) and at home with no network.
>
> BizMark
>
> Dave Peterson Wrote:
> > I've gotten that error (one or two times vbg). But none that I could
> > attribute to an "on error resume next" line.
> >
> > #2. I use xl2003 and don't use many dialog sheets, but I almost always
> > have the
> > forms toolbar, drawing toolbar, and control toolbox toolbar visible.
> > As a test,
> > I added a dialog sheet and then swapped to the VBE. Nothing bad
> > happened.
> >
> > #3. I don't recall ever having excel crash when I had range names that
> > had
> > errors. (My code crashed--but not excel.
> >
> > #4. I've never seen excel crash because of this, either.
> >
> > I've found that excel crashes with I do something wrong with an API
> > function or
> > maybe even a corrupt workbook. Or other things that I couldn't
> > pinpoint
> > (non-reproducible types).
> >
> >
> >
> >
> > BizMark wrote:-
> >
> > Dave Peterson Wrote:-
> > I'm not sure what error you're writing about (in xl97),-
> >
> > I think millions of Excel users around the world are only too aware
> > what error message this is.
> >
> > Surely you can't tell me you've NEVER had this?
> >
> > "Microsoft Excel has performed an Illegal Operation and will be shut
> > down. If the problem persists, please contact the software vendor".
> >
> > Or, in later versions,
> >
> > "Microsoft Excel has encountered a problem and needs to close. We
> > apologise for any inconvenience. Do you want to submit an error
> > report?"
> >
> > There are a myriad causes for this behaviour, some are consistently
> > repeatable and therefore trappable, and some are not. Any situation
> > which causes these to happen with any regularity, it is useful to
> > people to point out as it can prevent them from losing many man hours
> > or days of work. Some a
> >
> > 1. Using On Error Resume Next then On Error Goto 0
> > 2. Editing VBA code when the 'Forms' toolbar was open on a
> > Dialogsheet
> > before switching to the VBE, then saving in the VBE
> > 3. Operating on range names in macros when the name definitions
> > contain
> > #REFs etc.
> > 4. One user running macro in a workbook, another user having the same
> > workbook open as Read-Only, then the other user does 'Save As' to
> > make
> > a copy - result - Illegal Operation/Encountered a Problem error
> > occurs
> > for user running the macros.
> >
> > There are many others that don't spring to mind straight away.
> > Sometimes this error cannot be explained.
> >
> > However, I think StinkeyPete needs to realise that it needn't be an
> > issue just because somebody has issued a warning over his solution.
> >
> > BizMark
> >
> > --
> > BizMark-
> >
> > --
> >
> > Dave Peterson
>
> --
> BizMark

--

Dave Peterson