Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suppress error message (97sr2)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress error message (97sr2)

    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.

  3. #3
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress error message (97sr2)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress error message (97sr2)

    In real life, as in VBA, you run the risk of a serious crash if you ignore errors. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress error message (97sr2)

    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suppress error message (97sr2)

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

    <pre>If ActiveSheet.FilterMode Then _
    ActiveSheet.ShowAllData</pre>


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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress error message (97sr2)

    You should also insert:

    <pre> On Error GoTo 0
    </pre>


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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress error message (97sr2)

    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.

  9. #9
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress error message (97sr2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •