Action On Filter Selection 2

[eti3nne]

On selecting from a dropdown in the autofilter in Excel, the down arrow changes to blue.

I would like to make the column ,or the selected data, change colour, and ideally change back to a 'standard' colour on selecting 'All'.

Any ideas?

Thanks for looking.

 

[xlbo]

No easy way to do this as there are no events associated with filtering

Best bet would be to have a Subtotal() formula looking at the filter range - this would recalc when you change the filter. You can then utilise the Calculate event of the worksheet to iterate through the Filters collection of the autofilter object, checking the field & criteria value for each. That's probably enough to be going on with for now - you'll need to find the last used row but there are fAQs on that in this forum FAQ section. There are some good examples in the help files for using the calculate event and for using the Filters collection. Have a look there, see how you get on and post back with your code and any specific questions if you get stuck

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[Gavona]

That would be a useful bit of code - please do post back a solution! Also I feel sure that I once saw an add-in function that would display the filter criteria themselves in (say) the cell above the field heading. If you can find that function then I would guess it might help you.

Thanks,

Gavin

 

[eti3nne]

Thanks both,

xlbo,
How do i collect the filters column position from code, so that I can apply my requirements to the, as you suggested, on calculate event?

Gavona, yes I have a copy of that particular bit of code (display the filter criteria themselves in the cell above the field headings) and am currently using it. Also I have attached the link below, from that particular, very handy, Excel tips source site.

http://j-walk.com/ss/excel/usertips

Thanks for your interest.

 

[Gavona]

Hmm, just a thought...if you are using that code then could you not simply use conditional formatting to test for non blanks and apply the colour?

* for the link.

Thanks,

Gavin

 

[Gavona]

It worked - I used a conditional format condition of =LEN(B$9)<>0 where B$9 is the cell containing the FilterCriteria function that you provided the link to.

(I applied it to a database with 1.5m cells with no problems)

Thanks,

Gavin

 

[xlbo]

This should get you going:

Sub Show_Me_The_Filters()
Dim fRange As String, TheColumn As String

With ActiveSheet.AutoFilter

fRange = .Range.Address
ctr = 1

For Each fl In ActiveSheet.AutoFilter.Filters
    If fl.On = True Then
        TheColumn = Left(Range(fRange).Cells(1, 1).Offset(, ctr - 1).Address(0, 0), 1)
        MsgBox "Filter is on column " & TheColumn & vbCrLf & _
                "Filter value" & fl.Criteria1
    End If
    ctr = ctr + 1
Next

End With

End Sub

This loops through each filter in the autofilter object and if its "On" propety is true (ie it is being used), it uses the Offset function based on the filter range to return the cell in row 1 of the column that has been filtered on. It then uses the LEFT function to return just thecolumn portion of this.

Please note that I have never done this before and all of the above is based on looking at the help file examples and using the information in there. Please feel free to do the same...

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[eti3nne]

xlbo,
Thanks again. That has indeed got me started, not quite there yet though, but persevering ( my coding skills are a bit dodgy, but this way I can learn, hopefully)

Gavona,
Thank you for that. Be aware though that filters can only hold a maximum of ?1000 unique values. Any more than that and Excel just ignores them! Not even a warning message!. I don't know the exact limit on this, but I have seen this 'feature' in (in)action.

 

[xlbo]

Be aware though that filters can only hold a maximum of ?1000 unique values

Not actually true. They display only the 1st 1000 unique records but can hold as many as necessary.

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[eti3nne]

Thanks for that clarification. I knew what I meant, just didn't explain properly.

PS Is it exactly 1000 or do you know the exact limit?, and from where is this sort of detail available?

PPS. I neglected to give you a * earlier. (It should be there now). Thanks.

 

[xlbo]

LOL - no worries. It is exactly 1000 unique entries

Type "filter" in the help search and choose "Filter a list"
This is what is shown:

Filter a list
You can apply filters to only one list on a worksheet at a time.

Click a cell in the list you want to filter.
On the Data menu, point to Filter, and then click AutoFilter.
Filter for the smallest or largest number

Filter a list for rows that contain specific text

Filter for blank or nonblank cells

Filter for numbers greater than or less than another number

Filter for a number equal to or not equal to another number

Filter for the beginning or end of a text string

When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered list.

Only the first 1000 unique entries in a list appear when you click the arrow.


Another VERY useful search phrase is "Specifications"
This brings up a lot of info about max / min values for all sorts of objects / functions etc

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question