Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reset Filters at save (2002)

    I have a workbook with several sheets, some of which have filters. I have code attached to the save event that reset's all filters to NULL before save. This code is in "This Workbook".

    I have (in a standard module) a piece of code that highlights the top cell of any filtered columns. This is called from each individual sheet code.

    My problem is: If more than one sheet is filtered at the save event, all filters are reset, but only the active sheet's filter colour is reset. I am attaching the code, I think the problem is with "Set ws = wb.ActiveSheet", but can't find a way around it. Help.....

    Added later:

    I think "Set ws = wb.ActiveSheet" is OK. Maybe I need to add the colour reset to the save event following the filter reset, but I am unsure how?
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reset Filters at save (2002)

    I am adding a sample file.
    If you filter column G in both sheets 1 & 2 to 1.00, the header's of column G will show red on both sheet's. Saving the file will reset both filters to non-filtered, and the colour of the active sheet header will return to normal, but the additional sheet(s) remain red. I think I need to reset this at the save event, but am unable to find the correct syntax. Thanks
    Attached Files Attached Files

  3. #3
    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: Reset Filters at save (2002)

    The problem is that the coloring of the filters work ONLY on the active worksheet. You need to make it general to work an any worksheet.

    Try the attached code modifications

    Steve
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reset Filters at save (2002)

    Thankyou Steve, very much appreciated!!

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reset Filters at save (2002)

    Steve, trouble is, as one thing gets fixed, another falls apart... I now need to bring these two projects (back) together.

    I have attached a piece of code that is from a different workbook, and looks up the workbook that you kindly ammended the code for. Prior to this being added, the look up worked fine, keeping in mind that it previously contained no code, and no formula's, and now contains both, (one =today() formula to allow the calculation).

    When I run the code in the other workbook, which opens, sorts and looks up this one, since the code has been added, there is a conflict and the routine never finishes (until I quit via task manager). I have tried removing the volatile formula, no change. Is there a way to open the file with macro's strictly disabled? Or can you see any other reason for the conflict?

    It may be difficult to provide a sample file, but please let me know if that is the only way. I have put some comments in the text file.

    Many Thanks
    Attached Files Attached Files

  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: Reset Filters at save (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 11-Dec-08 20:56. Added PS)</P>I would need a sample file to work through or you would have to debug and be more specific on what the problem is.

    Steve

    PS one thought is that the code is just very slow. It runs the setting the color at every calculation. you might try turning calculations to manual when you start your routine, then turn it to auto and then calculate...

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reset Filters at save (2002)

    Steve,
    Only the one thought was needed, spot on! Thankyou.

Posting Permissions

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