query on UDFs and 'target'

[icestationzbra]

hi,

i have a query regarding the UDF 'showfilter' that i found on debra'
website. this function is used to highlight a filter that is currentl
on. the function returns a value to a cell. that value is then used i
conditional formatting to change the colour of the cell containing th
filter that is active.

i was wondering if there is a way we could use the 'showfilter
function directly in conditional formatting 'formula is' field. i
would reduce the number of steps down to one.

i was also not able to understand a few lines from the code. if someon
would be benevolent enough to explain it to me, i would be grateful.

Set sh = rng.Parent

If Intersect(rng.EntireColumn, frng) Is Nothing Then

lngOff = rng.Column - frng.Columns(1).Column + 1

If Not sh.AutoFilter.Filters(lngOff).On Then

Set filt = sh.AutoFilter.Filters(lngOff)

i also wanted to know if i could use a range of cells while comparin
'target' in the selectionchange event. that is, currently i hav
graduated to using 'target.row=2' and 'target.column=3' to check fo
cell 'C2'. is there a way to check if C2 (or the activecell) falls i
the range 'A1:C10'? i was not able to figure out by myself.

*****

the whole code is pasted below, with due credit to the author:

*****

Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtota
command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B

Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet

Set sh = rng.Parent

If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If

Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else

lngOff = rng.Column - frng.Columns(1).Column + 1


If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else

Set filt = sh.AutoFilter.Filters(lngOff)

On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator

If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If

ShowFilter = sCrit1 & sop & sCrit2

End If

End If

End Function

*****

thanks,

mac

 

[Dave Peterson]

Sure, you could use it the CF formula. But remember it returns a leading =
sign:

=showfilter(B5)="=3"

When I filtered on the #3.


#1: Set sh = rng.Parent

Sh was declared as a worksheet. Rng was passed to the function as a range. The
parent of a range it the worksheet that holds that range. (the parent of A1 on
sheet99 is Sheet99.)


#2. If Intersect(rng.EntireColumn, frng) Is Nothing Then

fRng was the filterrange. rng.entirecolumn is the whole column that has that
cell.

If rng is b92, then rng.entirecolumn is all of B.

if the intersection is nothing (the with that cell in it isn't part of the
autofilter range)


#3. lngOff = rng.Column - frng.Columns(1).Column + 1

lngOff is declared as a long (a whole number).

If you filter A1:Z99, and you want to look at the filter in column D, you'd go
over 3 columns.

Rng.column = 4
frng.columns(1).column is the column number for column A (1).
4-1+1 evaluates to 4.

sh.AutoFilter.Filters(lngOff)
says to look at the "4" column in the autofilter range.

If you thought you could just take the column of the filter, then what would
happen if you filtered Q13:AB77. That expression gets the right column no
matter where your data is located.

#4. If Not sh.AutoFilter.Filters(lngOff).On Then
(I bet #3 answered most, but this just checks to see if that column is turned on
(blue arrow in the worksheet).

#5. Set filt = sh.AutoFilter.Filters(lngOff)
Filt is declared as a filter.

this is an object in excel that has lots of properties that can be
inspected/changed. By using an object variable, it makes it easier to
manipulate it/inspect it.


#6. I like this format:

'only one cell at a time...
if target.cells.count > 1 then exit sub
if intersect(target,me.range("A1:C10")) is nothing then exit sub

 

[icestationzbra]

hi dave,

thanks for the reply.

i tried it with the syntax given by you, with the leading '=' sign.
am not able to get the conditional formatting on the cell. may be i a
not doing exactly as what you mentioned. let me try to reproduce m
steps here.

as per the code, if no filter is active on the page, "No Active Filter
should be returned.

now, let us say i clicked on C1 and in the 'CF-Formula Is' field,
entered the first condition as:

=showfilter(C1)="=No Filter Active"

if a particular filter condition is not active, "No Conditions" shoul
be returned.

and hence, my second condition as:

=showfilter(C1)<>"=No Conditions"

would that be what you mentioned?

thanks,

mac

 

[Dave Peterson]

Er, I said this, but that's because I filtered using = something.

You'll get "No Filter Active" if there is no active filter.

Try putting that formula (in the code) in a nearby cell and test the heck out of
it (well, at least more than I did!).

You can get lots of prefixes.

 

[icestationzbra]

dave,

i dont think i give enough attention to detail, and that was th
problem here.

it was clearly mentioned in debra's xl file that there is an extr
space in "No Active Filters ", and i missed it. once had that right
everything worked like a dream just like you said.

after reading through your explanation of that code, i was able to ge
a hang of it correctly. more so, as you mentioned, point #3 covered th
whole ground for me. with that knowledge, i was able to thin the cod
down to the bare minimum to make it function as intended.

here is the emaciated version of the code with due credit to th
original author (jon oglivy) and debra.

thanks,

mac.

*****
'author: jon oglivy
'site: contextures

Public Function CheckFilter(rngCell As Range)

Dim lngClmOfst As Long
Dim rngFilter As Range
Dim ws As Worksheet

Set ws = rngCell.Parent

On Error Resume Next

If ws.FilterMode = False Then Exit Function

Set rngFilter = ws.AutoFilter.Range

If Intersect(rngCell.EntireColumn, rngFilter) Is Nothing Then Exi
Function

lngClmOfst = rngCell.Column - rngFilter.Columns(1).Column + 1

If ws.AutoFilter.Filters(lngClmOfst).On
Then

CheckFilter = "Y"

End If

End Function

****

 

[icestationzbra]

forgot to mention one point in my earlier post.

now i can make do with only one condition in the conditiona
formatting, instead of the two earlier

 

[Dave Peterson]

If you don't like that extra space, you could use Trim() to get rid of it:

=showfilter(B2)&trim(CHAR(SUBTOTAL(9,B3)*0+32))
or even
=showfilter(B2)&text(SUBTOTAL(9,B3)*0,"")

 

[icestationzbra]

dave,

currently, what i am doing is using the function directly in CF-Formul
Is field as '=checkfilter(a1)="Y"'. i am not using the 'subtotal
formula at all, which was my intention, and that is why i went throug
the rigmarole of trying to understand the program and change it. hence
i do not need to use trim function. but, your point is taken.

however, that gives rise to another question. could you please tell me
why did the extra trailing space come up in the first place?

thanks,

mac

 

[Dave Peterson]

I thought you were using the formula in Deb's example (from Tom):

=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)

Tom put that subtotal() function to force the formula to recalculate when the
filter changed.

And he used:
....&CHAR(SUBTOTAL(9,B3)*0+32)

Since 0*anything is 0 (including whatever the subtotal is), it's equivalent to
char(0+32) or just char(32) which is the space character.

You could put a single character in a bunch of cells and do:
=code(a1)
(space character will return 32)

Or you can get Chip Pearson's CellView program.
http://www.cpearson.com/excel/CellView.htm
(well worth the download if you ever really need to know.)