Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter database range (XL97 sr2 on Win2000)

    Is there way to show the range of a simple filter database (set via Data | Filter | AutoFilter) on the actual sheet? For example could a coloured border be applied around the range?

    stuck

  2. #2
    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 database range (XL97 sr2 on Win2000)

    Try this. It adds a red rectangle (3 point line) around the filtered range (change as desired). Add the macro to the worksheet object of the sheet with the filter.

    This will not affect any formatting you add to any of the cells in the worksheet.

    Steve

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim shpFilter As Shape
    Dim rngFilter As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveSheet.Shapes("objFilterOutline").Delete
    On Error GoTo 0

    If ActiveSheet.AutoFilterMode Then
    Set rngFilter = ActiveSheet.AutoFilter.Range
    With rngFilter
    Set shpFilter = ActiveSheet.Shapes.AddShape _
    (msoShapeRectangle, .Left, .Top, .Width, .Height)
    End With
    With shpFilter
    .Name = "objFilterOutline"
    .Fill.Visible = msoFalse
    With .Line
    .ForeColor.RGB = RGB(255, 0, 0)
    .Weight = 3
    .Style = msoLineSingle
    End With
    End With
    End If
    Application.ScreenUpdating = True
    Set rngFilter = Nothing
    Set shpFilter = Nothing
    End Sub</pre>


  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter database range (XL97 sr2 on Win2000)

    Thanks but so far I get an error ("the specifed value is out of range") at :
    Set shpFilter = ActiveSheet.Shapes.AddShape _
    (msoShapeRectangle, .Left, .Top, .Width, .Height)

    stuck

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter database range (XL97 sr2 on Win2000)

    I really have tried with this one but I still can't past the "specifed value is out of range" error when the code tries to set shpFilter =ActiveSheet.....etc

    Can any one bail me out?

    stuck <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  5. #5
    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 database range (XL97 sr2 on Win2000)

    What is the value of the 4 parameters when you get the error?
    What is the rngFilter.address?

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter database range (XL97 sr2 on Win2000)

    This is the first of two posts each with an example file attached.

    1) The attached file BorderFilterRange_dud.xls contains the code from above but it doesn't work. It is a single worksheet that was originally the first page of a multisheet book. The workbook was created in XL97 but has subsequently been opened and resaved many times in XL2000.

    The story continues in my next post.

    stuck

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter database range (XL97 sr2 on Win2000)

    This is the second of two posts each with an example file attached.

    2) The attached file BorderFilterRange.xls also contains the code from above but it does work. It is a single worksheet, created cleanly in XL2000. Apart from their different origins I can see no difference between the two books.

    stuck

Posting Permissions

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