Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    disabling autofilter macro (Excel97 SR-2)

    I would like to create a macro so when you open a particular file, it checks to see if the autofilter is activated. If it is I would like the macro to automatically disable the autofilter <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

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

    Re: disabling autofilter macro (Excel97 SR-2)

    Open that workbook.
    Activate the Visual Basic Editor (Alt+F11).
    Insert a module (Insert/Module).
    Type the following macro:

    Sub Auto_Open()
    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    End Sub

    Return to the workbook. Save and close it.
    From now on, it should automatically turn off AutoFilter in the active worksheet.

    (For the experts: why doesn't this work in the Workbook_Open event handler?)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disabling autofilter macro (Excel97 SR-2)

    Some things do not work when called from inside a non-normal module. Solution is to call a sub in a normal module from the "special" module (sheet module, class module, userform module, etc).

    There is a good reason to put as little code as you can in event modules: they are much harder to "Clean", if at all possible (e.g. one cannot delete the thisworkbook module to import it again).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: disabling autofilter macro (Excel97 SR-2)

    Hans,

    Out of curiosity I tried it from the Workbook_Open and it worked fine. (XL97)

    Not sure you need an On Error Resume Next, though.

    Andrew

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

    Re: disabling autofilter macro (Excel97 SR-2)

    Hello Andrew,

    Duh, it didn't work for me in Excel 97 when I was answering the question. In the meantime I quit and restarted Excel and now it *does* work from the WorkBook_Open! Probably, I had been experimenting too much.

    The "On Error Resume Next" is meant to prevent an error message if the active sheet is a chart sheet.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: disabling autofilter macro (Excel97 SR-2)

    Hans,

    You must have had EnableEvents set to false, which does not intefere with Auto_Open.

    Chart sheets I had neglected to think about, as any charts I ever use get embedded in standard worksheets.

    Andrew

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

    Re: disabling autofilter macro (Excel97 SR-2)

    Andrew,

    I didn't have EnableEvents set to False (AFAIK), but I had had Excel open for 2 or 3 hours with lots of workbooks, many of them with Workbook_Open, WorkBook_Activate, Workbook_Deactivate and Workbook_Close events, creating and deleting menu bars, tool bars, etc. I guess Excel just got confused after a while - teaches me to close and reopen Excel from time to time.

    Hans

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: disabling autofilter macro (Excel97 SR-2)

    To quote Jan Karel
    <hr>There is a good reason to put as little code as you can in event modules<hr>
    Andrew

Posting Permissions

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