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

    Filter reset at save (2002)

    Assistance with the attached code correction very much appreciated! I have detailed the problem attached.

    Thanks
    Attached Files Attached Files

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

    Re: Filter reset at save (2002)

    You as designer should know what the filtered ranges are. I don't think it's easy, if possible at all, to find out which range (if any) is used by AutoFilter. (Perhaps someone will correct me, then I'll learn something new too.)

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

    Re: Filter reset at save (2002)

    The function was not written by me, I came accross this online. I forget where. I can use it as is, except fot the reset home bit which does not work, but I was hoping that I could not refer directly to specific ranges, so that it could be easily adapted to other situations.

  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: Filter reset at save (2002)

    You get the runtime error since you are trying to select a range that is not on the active sheet. The code loops through all the sheets, but only one is active and only on that sheet can they be selected.

    The range of the autofilter in the "wsh" worksheet is:
    wsh.Autofilter.Range

    I don't understand exactly what your questions/problems are, could you elaborate?

    Steve

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

    Re: Filter reset at save (2002)

    Thanks Steve.

    I have sorted the issue of returning all sheets to the home position, using the code below:

    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
    'wsh.AutoFilter.Range.Interior.Color = RGB(153, 153, 255) 'Fixed version of ColourIndex = 17
    End If

    wsh.Activate
    wsh.Range("A2").Select
    wsh.Range("A1").Select

    Next wsh

    End Sub

    wsh.AutoFilter.Range >>> Is the entire filtered range. What I need is the top row of the filtered range. ?? Thanks

  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: Filter reset at save (2002)

    wsh.AutoFilter.Range.rows(1)

    Steve

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

    Re: Filter reset at save (2002)

    thanks, nearly there. This now resets the filters on all worksheets in workbook before save, but I want to reactivate the active worksheet at the end, something like: (but my syntax is wrong)

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Application.ScreenUpdating = False

    Dim wsh As Worksheet
    Dim thisws As Worksheet

    Set thisws = activeworksheet

    For Each wsh In Me.Worksheets

    If wsh.FilterMode Then
    wsh.ShowAllData
    wsh.AutoFilter.Range.Rows(1).Interior.Color = RGB(153, 153, 255) 'Fixed version of ColourIndex = 17
    End If

    wsh.Activate
    wsh.Range("A2").Select
    wsh.Range("A1").Select

    Next wsh

    thisws.Activate

    Application.ScreenUpdating = True

    End Sub

  8. #8
    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: Filter reset at save (2002)

    Set thisws = activesheet

    Your code presumes that the activesheet will always be a worksheet. If it could be a chartsheet at some point then use

    Dim thisws As Variant
    Set thisws = activesheet

    Steve

  9. #9

Posting Permissions

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