Suppress error message (97sr2)

Topic

New Reply

A simple one: I have some worksheets where autofilter is applied, for various criteria, by clicking one of a number of big grey buttons. There’s an even bigger grey button marked SHOW ALL which – you’ve guessed it – removes (ie un-applies) all the filters.
If I click SHOW ALL when it’s already showing all, ie no filters applied, it’s hugely understandable that XL gets confused and invites me to debug it.
What line of code can I put in the ‘show all’ module to encourage XL to think not so harshly of the user, ie just carry on?

Reply | Quote

Replies

Insert a line

On Error Resume Next

above the offending instruction. This line tells Excel to ignore errors. In more complicated situations, that is dangerous, but in this case, it’ll be allright.

Reply | Quote

Thanks, Hans.
I figure the general warning applies to certain other areas of human discovery, like Marriage, perhaps?

Reply | Quote

In real life, as in VBA, you run the risk of a serious crash if you ignore errors.

Reply | Quote

In real life, as in VBA, you run the risk of a serious crash if you ignore errors.

Reply | Quote

Thanks, Hans.
I figure the general warning applies to certain other areas of human discovery, like Marriage, perhaps?

Reply | Quote

If the error is as described from the showalldata property, I would use:

If ActiveSheet.FilterMode Then _
    ActiveSheet.ShowAllData

It doesn’t mess at all with the error trapping. I always prefer to prevent rather than trap an error (if possible)
Steve

Reply | Quote

If the error is as described from the showalldata property, I would use:

If ActiveSheet.FilterMode Then _
    ActiveSheet.ShowAllData

It doesn’t mess at all with the error trapping. I always prefer to prevent rather than trap an error (if possible)
Steve

Reply | Quote

You should also insert:

    On Error GoTo 0

after the offending line of code. Otherwise you will ignore all errors for the rest of the code.

Reply | Quote

In longer code, absolutely!

I assumed that the “even bigger grey button marked SHOW ALL” only turned off the filter and did nothing else. Since On Error statements operate at the procedure level, there is no harm in omitting On Error GoTo 0.

But again, I agree that you must reset error handling if there is more code.

Reply | Quote

Thanks to all of you, friends.
A quick answer to my question, and some extra wisdom to put in the bank…

Reply | Quote

Thanks to all of you, friends.
A quick answer to my question, and some extra wisdom to put in the bank…

Reply | Quote

In longer code, absolutely!

I assumed that the “even bigger grey button marked SHOW ALL” only turned off the filter and did nothing else. Since On Error statements operate at the procedure level, there is no harm in omitting On Error GoTo 0.

But again, I agree that you must reset error handling if there is more code.

Reply | Quote

You should also insert:

    On Error GoTo 0

after the offending line of code. Otherwise you will ignore all errors for the rest of the code.

Reply | Quote

Insert a line

On Error Resume Next

above the offending instruction. This line tells Excel to ignore errors. In more complicated situations, that is dangerous, but in this case, it’ll be allright.

Reply | Quote

John,

I slapped this together to test out what you were seeing. I run Office 2000 and XP and so could not test with 97.

Reply | Quote

John,

I slapped this together to test out what you were seeing. I run Office 2000 and XP and so could not test with 97.

Reply | Quote