Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any way to change the default colours for the filter arrows in Excel 2002?

    Unselected is black and selected is blue - but in a large spreadsheet (30+columns) it is very difficult to see which columns have been filtered. It would be much better if they could be defaulted to say red or yellow.

    I would also love to be able to change the default fill colour on the toolbar button from yellow to green!

  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
    Unfortunatley they are hard-coded....

    BUT, you may find this technique for highlighting the columns useful: http://lounge.windowssecrets.com/ind...dpost&p=441861

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ecellent! Many thanks.

    A side effect is that I can use the formula SUBTOTAL(9,A:A) to count the rows displayed by added a hidden column withe 1s in it and pointing the formula to that column - unless there is a more elegant solution?

    Another related question. The sheet that I am filtering is populated temporarily with data gathered from other sheets by macros. Is there a line that I could add to the start of those macros to clear any filters that are set?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use:
    Code:
       With ActiveSheet
          If .AutoFilterMode Then .AutoFilter.Range.AutoFilter
       End With
    to actually remove the autofilter completely, or:
    Code:
       With ActiveSheet
          If .AutoFilterMode Then .ShowAllData
       End With
    to just have all data visible, but leave the autofilter set up.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rory

    Many thanks for all this - it is working perfectly.

    I answered my own question on counting the rows - I used =SUBTOTAL(3,A8:A1000) instead of =SUBTOTAL(9,A:A) to count occupied rows below my headers.

    Best wishes

    Peter

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I spoke too soon!

    .showalldata will not work when I protect the sheet.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Have you allowed filtering?

    You could always just add code to unprotect the sheet, clear the filter, then reprotect.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory

    Yes - that is what I thought. Works fine if a filter is set when I run the macro, but fails if not.
    (Runtime error '1004':
    ShowAllData method of Worksheet Class failed)

    Where am I going wrong?

    Here is the code:

    Worksheets("Stud Book").Unprotect
    With ActiveSheet
    If .AutoFilterMode Then .ShowAllData
    End With
    Worksheets("Stud Book").Protect Contents:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True

    I want to protect the sheet allowing:
    Select unlocked cells
    Format cells
    Sort
    Use Autofilter

    Best wishes

    Peter

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Two things need changing - you need to specify the same worksheet, and you actually need to test if a filter is in use:

    Code:
    With Worksheets("Stud Book")
       .Unprotect
       If .AutoFilterMode Then
          If .FilterMode then .ShowAllData
        End If
       .Protect Contents:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
    End With
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory

    Once again I am greatly indebted to you for your help.

    I had thought of simply removing the autofilter and then replacing it, but your solution is much simpler.

    I had also added a line to reset the fill colour, but that seems unnecessary with your solution.

    Best wishes

    Peter

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have another question.

    On the same worksheet, I wish to change the format of a range of cells depending on which macro I run to populate the sheet. So I wish to add a line to the macro (where it has unprotected the sheet) that says:

    1 Worksheets("Stud Book").Range("AH8:AH1000") change the format to whole number (no decimal places)

    2 Worksheets("Stud Book").Range("AH8:AH1000") change the format to allow a four digit number which may have a leading zero.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You would need to set the NumberFormat property to either "#,##0;-#,##0;0" or "0000" respectively.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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