If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query on UDFs and 'target'
hi,
i have a query regarding the UDF 'showfilter' that i found on debra's website. this function is used to highlight a filter that is currently on. the function returns a value to a cell. that value is then used in conditional formatting to change the colour of the cell containing the 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. it would reduce the number of steps down to one. i was also not able to understand a few lines from the code. if someone 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 comparing 'target' in the selectionchange event. that is, currently i have graduated to using 'target.row=2' and 'target.column=3' to check for cell 'C2'. is there a way to check if C2 (or the activecell) falls in 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 subtotal 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. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
query on UDFs and 'target'
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 " wrote: hi, i have a query regarding the UDF 'showfilter' that i found on debra's website. this function is used to highlight a filter that is currently on. the function returns a value to a cell. that value is then used in conditional formatting to change the colour of the cell containing the 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. it would reduce the number of steps down to one. i was also not able to understand a few lines from the code. if someone 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 comparing 'target' in the selectionchange event. that is, currently i have graduated to using 'target.row=2' and 'target.column=3' to check for cell 'C2'. is there a way to check if C2 (or the activecell) falls in 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 subtotal 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. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#3
|
|||
|
|||
query on UDFs and 'target'
hi dave,
thanks for the reply. i tried it with the syntax given by you, with the leading '=' sign. i am not able to get the conditional formatting on the cell. may be i am not doing exactly as what you mentioned. let me try to reproduce my 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, i entered the first condition as: =showfilter(C1)="=No Filter Active" if a particular filter condition is not active, "No Conditions" should be returned. and hence, my second condition as: =showfilter(C1)"=No Conditions" would that be what you mentioned? thanks, mac. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
query on UDFs and 'target'
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 " wrote: hi dave, thanks for the reply. i tried it with the syntax given by you, with the leading '=' sign. i am not able to get the conditional formatting on the cell. may be i am not doing exactly as what you mentioned. let me try to reproduce my 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, i entered the first condition as: =showfilter(C1)="=No Filter Active" if a particular filter condition is not active, "No Conditions" should be returned. and hence, my second condition as: =showfilter(C1)"=No Conditions" would that be what you mentioned? thanks, mac. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#5
|
|||
|
|||
query on UDFs and 'target'
dave,
i dont think i give enough attention to detail, and that was the problem here. it was clearly mentioned in debra's xl file that there is an extra 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 get a hang of it correctly. more so, as you mentioned, point #3 covered the whole ground for me. with that knowledge, i was able to thin the code down to the bare minimum to make it function as intended. here is the emaciated version of the code with due credit to the 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 Exit Function lngClmOfst = rngCell.Column - rngFilter.Columns(1).Column + 1 If ws.AutoFilter.Filters(lngClmOfst).On Then CheckFilter = "Y" End If End Function ***** --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
query on UDFs and 'target'
forgot to mention one point in my earlier post.
now i can make do with only one condition in the conditional formatting, instead of the two earlier. --- Message posted from http://www.ExcelForum.com/ |
#7
|
|||
|
|||
query on UDFs and 'target'
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 " wrote: dave, i dont think i give enough attention to detail, and that was the problem here. it was clearly mentioned in debra's xl file that there is an extra 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 get a hang of it correctly. more so, as you mentioned, point #3 covered the whole ground for me. with that knowledge, i was able to thin the code down to the bare minimum to make it function as intended. here is the emaciated version of the code with due credit to the 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 Exit Function lngClmOfst = rngCell.Column - rngFilter.Columns(1).Column + 1 If ws.AutoFilter.Filters(lngClmOfst).On Then CheckFilter = "Y" End If End Function ***** --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#8
|
|||
|
|||
query on UDFs and 'target'
dave,
currently, what i am doing is using the function directly in CF-Formula 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 through 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. --- Message posted from http://www.ExcelForum.com/ |
#9
|
|||
|
|||
query on UDFs and 'target'
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.) "icestationzbra " wrote: dave, currently, what i am doing is using the function directly in CF-Formula 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 through 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. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|