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

    Reset Filter before save (2002)

    I have a spreadsheet that acts as a database which contains 60k+ rows of data in 10 columns. I need to prevent the spreadsheet from being saved with the data filtered. How can I achieve this?

    Also, I'm sure that I have read (but can't find it now) that it is possible to highlight a filtered column, better than the standard filter arrow turning blue, so that filtered data is much easier identified?

    Thanks

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

    Re: Reset Filter before save (2002)

    You could create the following event procedure in the ThisWorkbook module of the workbook:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsh As Worksheet
    For Each wsh In Me.Worksheets
    If wsh.FilterMode Then
    wsh.ShowAllData
    End If
    Next wsh
    End Sub

    This will turn off filter mode before the workbook is saved.

    About AutoFilter: try Rory's <!post=Autofilter Highlighter Add-in (All),605725>Autofilter Highlighter Add-in (All)<!/post>.

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

    Re: Reset Filter before save (2002)

    Hans, Thankyou for the event, that works perfectly for me. Rory's autofilter highlighter add-in is also great for my home pc. But..... Could this be incorporated into a specific workbook? without it having to be distributed as an add-in?

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

    Re: Reset Filter before save (2002)

    As rory wrote
    <hr>Note: the code is unprotected so can be adapted to your own requirements as necessary.<hr>
    You can copy the code into your workbook and modify it as needed. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

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

    Re: Reset Filter before save (2002)

    I was afraid you were going to say that....

    There's a lot of code in the add-in that I don't really understand. My requirement is basic, to highlight (red) the header of any filtered column's in a specific worksheet. I'll have a go (but I maybe back) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks

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

    Re: Reset Filter before save (2002)

    I cheated: <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Thanks to Debra Dagleish - Colour Filter Headings.

    Cheers.
    Attached Files Attached Files

Posting Permissions

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