Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Error Handler Query (Excel 2002)

    Hi

    I created this lilttle macro to remove filters from a worksheet the error_ handler works fine except it still appears at then end when the filters are removed.

    I would like it only to appear if there is no filter on, ie if it is filtered on clearing I do not need to see the message

    Many thanks

    Braddy

    Sub Showall()

    ' Showall Macro
    ' Macro recorded 23/09/2005 by Alan Bradshaw
    '

    On Error GoTo err_handler
    ActiveSheet.ShowAllData
    Range("A7").Select

    err_handler:
    MsgBox "There is no filter on"


    End Sub
    If you are a fool at forty, you will always be a fool

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

    Re: Error Handler Query (Excel 2002)

    Just above the line

    err_handler:

    insert a new line

    Exit Sub

    This will make the macro terminate normally if there was no error.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Error Handler Query (Excel 2002)

    Hi Hans

    Thank you very much.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    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: Error Handler Query (Excel 2002)

    I know Hans answered your question, but just an FYI/tip:

    If you use the line:
    <pre>If ActiveSheet.FilterMode then ActiveSheet.ShowAllData</pre>


    instead of:
    <pre>ActiveSheet.ShowAllData</pre>


    You will not get an error at all. the error comes from trying to show all the data, when it is not filtered. The above suggestions first checks to see if it is filtered before trying to show all the data.

    Steve

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

    Re: Error Handler Query (Excel 2002)

    In addition to what Hans and Steve have said, you should always use a Resume statement to exit from an error handler routine. When you have an error and enter the error handler Excel is in a special mode known as interrupt mode. There are many restrictions one what can be done in interrupt mode, and failing to exit from interrupt mode can cause all kind of strange things to happen. I would change your code to:

    Sub Showall()

    ' Showall Macro
    ' Macro recorded 23/09/2005 by Alan Bradshaw
    '

    On Error GoTo err_handler
    ActiveSheet.ShowAllData
    Range("A7").Select
    exit_handler:
    Exit Sub

    err_handler:
    MsgBox "There is no filter on"
    Resume exit_handler
    End Sub
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Error Handler Query (Excel 2002)

    Hi

    Can I say a broad thank you to all who replied.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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