• Suppress error message (97sr2)

    Author
    Topic
    #396364

    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?

    Viewing 3 reply threads
    Author
    Replies
    • #742165

      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.

      • #742199

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

        • #742207

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

        • #742208

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

      • #742200

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

      • #742215

        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

      • #742216

        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

      • #742290

        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.

        • #742342

          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.

          • #742783

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

          • #742784

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

        • #742343

          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.

      • #742291

        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.

    • #742166

      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.

    • #742211

      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.

    • #742212

      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.

    Viewing 3 reply threads
    Reply To: Suppress error message (97sr2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: