Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto filter spinner controls (Excel 2003)

    Hi,
    I have a user who makes quite extensive use of the autofilter facility.
    He has a problem when using a combination of filters for a number of columns, that when a column is filtered the arrow on the control changes to a dark blue colour.
    He then has difficulty in seeing which columns have filters applied and which columns do not.
    Can the 'filtered' colour be changed to something other than dark blue?
    Can the 'arrow' object's size be increased so that the default dark blue colour is more prominent?
    Can the shape of the 'arrow' object be changed when a filter is applied?
    Any suggestion greatly received.
    Regards
    Zinger

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

    Re: Auto filter spinner controls (Excel 2003)

    I'm sorry, the shape and color of the arrow are hard-coded, as far as I know there is no way to alter them.
    The dropwdown arrow can be made a little bit larger (but not much) by changing the zoom percentage at which the sheet is viewed to 125% (the size doesn't increase further for larger zoom percentages).

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

    Re: Auto filter spinner controls (Excel 2003)

    Hi Rory,

    That's a nice add-in!

    I have one remark: in the SetHilites procedure, you conditionally set the caption of the toolbar button to "Highlight Filters", but at the end, you set it to "Clear filter highlights" unconditionally. Shouldn't this last statement be moved to just above Else?

  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

    Re: Auto filter spinner controls (Excel 2003)

    Hans,
    I've just this second posted a revised version using a new toolbar with a Set Highlights and a Clear Highlights button. It dawned on me that if you had two workbooks open with autofilters, you couldn't switch from one to the other and apply the highlighting if there's only one button. Hopefully this version is a little better! (I did say it was rough and ready... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Auto filter spinner controls (Excel 2003)

    Looks good!

  6. #6
    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

    Re: Auto filter spinner controls (Excel 2003)

    <P ID="edit" class=small>(Edited by rory on 05-Oct-06 10:26. [Version 1.3 (already): made the defined name local to the sheet so you can have more than one autofiltered sheet in a workbook.])</P>The workaround I usually use is to have the header cell of the filtered column(s) highlighted. I've just dropped the code I use into an add-in, which I am attaching to this post, which you can try out. It's a bit rough and ready but the code is unprotected so feel free to alter it or let me know if there are any particular issues with it.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  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

    Re: Auto filter spinner controls (Excel 2003)

    Yet another change - made the name local to the sheet so you can have more than one autofilter per workbook. I think that will be the last change for a while. If I ever get time I will add a colour picker to choose the highlight colour and probably a few other niceties...
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Auto filter spinner controls (Excel 2003)

    Here is a version with a colour picker. The colour preference is stored in the Windows Registry (so it is a global setting; a further tweak would be allow different settings for each workbook or even for each worksheet).

  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

    Re: Auto filter spinner controls (Excel 2003)

    Very nice! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filter spinner controls (Excel 2003)

    Rory and Hans,
    Many, big thanks. The user is over the moon about it and I'm just about to install it on his computer.
    Regards
    Zinger

  11. #11
    JimDandy
    Guest

    Re: Auto filter spinner controls (Excel 2003)

    This is great. I like it..One question, How do I get the tool bar to stay at the top or the bottom of the sheet, instead it always comes up in the middle of the page when I open a workbook, no matter where I move it too? thank you

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

    Re: Auto filter spinner controls (Excel 2003)

    In the AddMenuItem procedure, you can add a line to position the toolbar:

    With cbr
    .Enabled = True
    .Visible = True
    .Position = msoBarTop
    End With

  13. #13
    JimDandy
    Guest

    Re: Auto filter spinner controls (Excel 2003)

    That worked thanks

  14. #14
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filter spinner controls (Excel 2003)

    The user is extremely happy, however he did get caught out with the colour picker control, in that he selected a colour but didn't exit it with either the Ok or Cancel and then experienced some intermittent issues in Excel.
    Can the 'colour picker' retain the focus until you either click OK or Cancel?
    Regards
    Zinger

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

    Re: Auto filter spinner controls (Excel 2003)

    If all your users have Excel 2002 (XP) or later, you can change the line

    cc.hwndOwner = 0

    to

    cc.hwndOwner = Application.Hwnd

    in the ShowColor function in basColour. This will make the Excel application window the 'owner' of the colour dialog. The user won't be able to click in the Excel window while the dialog is open.

Page 1 of 3 123 LastLast

Posting Permissions

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