Auto filter spinner controls (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

(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.])

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

Reply | Quote

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?

Reply | Quote

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… )

Reply | Quote

Looks good!

Reply | Quote

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…

Reply | Quote

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).

Reply | Quote

Very nice!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

can you re-post this file? it appears to have been eaten in the great lounge melt-down.

thanks!

Reply | Quote

Rory’s version in post 605,725 uses the color picker code I proposed, and he added other enhancements. He has re-posted the attachment recently.

Reply | Quote

Hi Rory
I liked the look of this very much, so I installed it, absolutly nothing happens when I try to use it.

Any suggestions ?

Braddy

Reply | Quote

Do you have a data table with AutoFilter turned on?

Reply | Quote

Hi Hans

Yes i do.

Regards

Braddy

Reply | Quote

You should click inside the autofiltered list, then click the button.

And the result will only be visible if you have actually filtered at least one column.

Reply | Quote

Hi Hans

I have done both those things.

Regards

Braddy

Reply | Quote

Hi Hans

This is mystery to me, inside the zip file are two supposedly identical workbooks, the autofilter colour works on the one call detect filters, but does not work on the one called detect filters2.

At least not on my machine.

NB I do not take credit for the code.

Braddy

Reply | Quote

In the workbook ‘detect filters2.xls’, AutoFilter is present, but no filter has been set, so none of the column headings is coloured. If you have clicked ‘Highlight filters’, and then set a filter on one or more columns (for example, select ‘Alan’ in the dropdown list in A1), the column heading should be coloured.

Reply | Quote

Hi Hans

You are quite correct in what you say, I had used the filter,but nothing happened. Hower when I closed down Excel and re-opened it, everything worked fine.

As always in am in your debt.

Braddy

Reply | Quote

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

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

Reply | Quote

Attached is an add-in which allows you to highlight (with a background and font colour of your choosing) the header cells of any columns in an autofiltered list which currently have filters applied. This makes it easier to see what the current filters are, rather than having to rely on Excel’s built-in colouring of the dropdown arrows, which can be hard to see, particularly in large lists. Special thanks to Hans for his addition of the colour picker options!
Note: the code is unprotected so can be adapted to your own requirements as necessary.

Reply | Quote

Rory,

What a fantastically useful add-in. I hadn’t realised how much I needed it until I couldn’t see which column was filtered in a large table this morning.

THANK YOU

StuartR

Reply | Quote

Stuart,
Glad you find it useful!

Reply | Quote

Hi Rory,

Actually I downloaded thing exactly the same as yours in 30 Oct (Date created) from somewhere that I can not remember, and could be from your site? perhaps.
anyway, many-many thanks for this one

-indra-

Reply | Quote

Stumbled upon this today – and it is great. One question: I often use a “Show All” button added to my toolbar in order to reset all the filters – when I do that it does not appear to reset the highlighting. Apologies if this has been covered somewhere else in the thread.

Reply | Quote

The highlighting will be updated as soon as the worksheet is recalculated. Unfortunately, the Show All Data command does not necessarily cause the worksheet to be recalculated. You could force this by creating a macro like this:

Sub ShowAll()
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.Calculate
End Sub

and assigning this macro to a custom toolbar button.

Reply | Quote

Good information. Interesting that the spreadsheet I tested this on has several Subtotal functions above the filtered list – yet the “Show All” command did not initiate a recalculation (though I suppose Excel is smart enough to keep track of just the cells requiring a recalc).

Again – this is great – thanks to all who developed and contributed.

Reply | Quote

Hi Rory
The attached file contains couple of recommended changes to position the command bar:

The following line was added to ThisWorkbook(Code).Workbook_BeforeClose

        SaveSetting "Hilite", "Menu", "Top", .Top

The AddMenuItem procedure was changed
From:

  With cbr
    .Enabled = True
    .Visible = True
    .Position = GetSetting("Hilite", "Menu", "Position", msoBarFloating)
    .RowIndex = GetSetting("Hilite", "Menu", "RowIndex", msoBarRowLast)
    .Left = GetSetting("Hilite", "Menu", "RowIndex", 0)
  End With

To:

  With cbr
    .Enabled = True
    .Visible = True
    .Position = GetSetting("Hilite", "Menu", "Position", msoBarTop) 'msoBarFloating)
    .RowIndex = GetSetting("Hilite", "Menu", "RowIndex", msoBarRowLast)
    .Left = GetSetting("Hilite", "Menu", "Left", 0)
    .Top = GetSetting("Hilite", "Menu", "Top", 0)
  End With

Reply | Quote

Hi Rory
I have further modified the code to occupy less space in the toolbar area. The AddMenuItem procedure has been rewritten.

Reply | Quote

Hi Don,

I like the addition of the font color, and the dropdown is a good idea!
You forgot to include the code to initialize the colors from the saved settings (if available)
And I don’t understand why you added an optional argument to the AddMenuItem macro – to hide it from the macro list? It might be useful to run the macro manually if the toolbar has been messed up.

The attached version initializes the colors properly (and I moved all the “exit” code to DeleteMenuItem).

Reply | Quote

[indent]

---

to hide it from the macro list?

---

[/indent]Yes; It’s a habit I’ve picked up for that very reason as well as providing a sane exit path when things go amiss.

Reply | Quote

Hi,

How do I remove hilite filters.xla ?

I have a shared spreadsheet that is a bit unstable and I’m trying to simplify it as much as possible in an attempt to make it more reliable.

I don’t see it on the add-ins list and can’t find any reference to it on the spreadsheet/code, but I still get asked for it every time I load the spreadsheet.

Thanks,
Jim MacLeod

Reply | Quote

Was the Filter Highlighter toolbar inadvertently left behind? If so, delete the toolbar.

Or did you happen to set a reference to the add-in in Tools | References in the Visual Basic Editor?

Reply | Quote

Hi, thanks for the suggestions.

I’ve checked the toolbars in View | Toolbars and the “Filter Highlighter” entry is no longer there.

The only ticked references in VBE are : Visual Basic for Applications, Excel 9.0 object library, OLE Automation, Office 9.0 Object library and Forms 2.0 ObjectLibrary. There is no entry for highlight/hilite or anything similar.

JIm

Reply | Quote

Does the ThisWorkbook module of the problem workbook contain code, and if so, does that give a clue?
Otherwise, is there a macro in a standard module named Auto_Open?

Reply | Quote

There is no code in the ThisWorkbook module, and I can’t see any macro named Auto_Open.

I did spot something earlier in the thread (675,685) about hiding the macro from the macro list but I didn’t understand it. How do you unhide a hidden macro?

Jim

Reply | Quote

You would still see the macro in the Visual Basic Editor.

Check for the existence of defined names IsFilterOn, as suggested by Rory (and me), I think that is the most likely explanation.

Reply | Quote

Hi, yes the problem was with the defined name. Thanks everyone for the help.

Jim

Reply | Quote

Open the workbook, and check under Insert->Name->Define and see if you can locate a name called IsFilterOn – if so, delete it.
HTH

Reply | Quote

Perhaps a defined name created by the add-in was left behind.
Select Insert | Name | Define…
If there is a name IsFilterOn, delete it.
Click OK.
Repeat for each worksheet.

(If you have Pieterse’s Name Manager, you can use that to delete the IsFilterOn names)

Reply | Quote