Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect the filter mode ?
Hi all,
I need a function to see if the autofilter is defined (so activ) and the rows are selected I tried it by this Function FilterOn() If ActiveSheet.FilterMode = True Then ... here should be "TRUE" Else ..... here should be "FALSE" End If End Function Can someone help me ? -- Jamen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect the filter mode ?
Sub test()
MsgBox TickedFilters(ActiveSheet) End Sub Function TickedFilters(ws As Worksheet) As Long Dim cntOn As Long Dim af As AutoFilter, f As Filter If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn End Function As written the function doesn't prove any rows are hidden, if you need that look for hidden rows in ws.AutoFilter.Range Regards, Peter T "Jamen Lone" wrote in message ... Hi all, I need a function to see if the autofilter is defined (so activ) and the rows are selected I tried it by this Function FilterOn() If ActiveSheet.FilterMode = True Then .. here should be "TRUE" Else .... here should be "FALSE" End If End Function Can someone help me ? -- Jamen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect the filter mode ?
How should I use this function in a sheet ?
I tried =TickedFilters() in cell A1, but it doesn't work. sorry, but I'm firm in vba. Jamen "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... Sub test() MsgBox TickedFilters(ActiveSheet) End Sub Function TickedFilters(ws As Worksheet) As Long Dim cntOn As Long Dim af As AutoFilter, f As Filter If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn End Function As written the function doesn't prove any rows are hidden, if you need that look for hidden rows in ws.AutoFilter.Range Regards, Peter T "Jamen Lone" wrote in message ... Hi all, I need a function to see if the autofilter is defined (so activ) and the rows are selected I tried it by this Function FilterOn() If ActiveSheet.FilterMode = True Then .. here should be "TRUE" Else .... here should be "FALSE" End If End Function Can someone help me ? -- Jamen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect the filter mode ?
I'm not firm :-)
"Jamen Lone" schrieb im Newsbeitrag ... How should I use this function in a sheet ? I tried =TickedFilters() in cell A1, but it doesn't work. sorry, but I'm firm in vba. Jamen "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... Sub test() MsgBox TickedFilters(ActiveSheet) End Sub Function TickedFilters(ws As Worksheet) As Long Dim cntOn As Long Dim af As AutoFilter, f As Filter If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn End Function As written the function doesn't prove any rows are hidden, if you need that look for hidden rows in ws.AutoFilter.Range Regards, Peter T "Jamen Lone" wrote in message ... Hi all, I need a function to see if the autofilter is defined (so activ) and the rows are selected I tried it by this Function FilterOn() If ActiveSheet.FilterMode = True Then .. here should be "TRUE" Else .... here should be "FALSE" End If End Function Can someone help me ? -- Jamen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect the filter mode ?
You mean you want to use it as a UDF, try this
Function TickedFilters(cell As Range) As Variant Dim ws As Worksheet Dim cntOn As Long Dim af As AutoFilter, f As Filter On Error GoTo errH Set ws = cell.Parent If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn Exit Function errH: TickedFilters = Err.Description End Function in a cell =TickedFilters(A1)+NOW()*0 the cell ref can be any cell on the sheet, probably better to make it the same cell as the formula cell. the NOW stuff is to help it update if user changes a filter setting. Regards, Peter T "Jamen Lone" wrote in message ... How should I use this function in a sheet ? I tried =TickedFilters() in cell A1, but it doesn't work. sorry, but I'm firm in vba. Jamen "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... Sub test() MsgBox TickedFilters(ActiveSheet) End Sub Function TickedFilters(ws As Worksheet) As Long Dim cntOn As Long Dim af As AutoFilter, f As Filter If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn End Function As written the function doesn't prove any rows are hidden, if you need that look for hidden rows in ws.AutoFilter.Range Regards, Peter T "Jamen Lone" wrote in message ... Hi all, I need a function to see if the autofilter is defined (so activ) and the rows are selected I tried it by this Function FilterOn() If ActiveSheet.FilterMode = True Then .. here should be "TRUE" Else .... here should be "FALSE" End If End Function Can someone help me ? -- Jamen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
detect the filter mode ?
thanks .. that's exactly the thing I'm looking for :-)
you're great "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... You mean you want to use it as a UDF, try this Function TickedFilters(cell As Range) As Variant Dim ws As Worksheet Dim cntOn As Long Dim af As AutoFilter, f As Filter On Error GoTo errH Set ws = cell.Parent If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn Exit Function errH: TickedFilters = Err.Description End Function in a cell =TickedFilters(A1)+NOW()*0 the cell ref can be any cell on the sheet, probably better to make it the same cell as the formula cell. the NOW stuff is to help it update if user changes a filter setting. Regards, Peter T "Jamen Lone" wrote in message ... How should I use this function in a sheet ? I tried =TickedFilters() in cell A1, but it doesn't work. sorry, but I'm firm in vba. Jamen "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... Sub test() MsgBox TickedFilters(ActiveSheet) End Sub Function TickedFilters(ws As Worksheet) As Long Dim cntOn As Long Dim af As AutoFilter, f As Filter If Not ws.AutoFilter Is Nothing Then If ws.FilterMode Then Set af = ws.AutoFilter For Each f In af.Filters If f.On Then cntOn = cntOn + 1 Next End If End If TickedFilters = cntOn End Function As written the function doesn't prove any rows are hidden, if you need that look for hidden rows in ws.AutoFilter.Range Regards, Peter T "Jamen Lone" wrote in message ... Hi all, I need a function to see if the autofilter is defined (so activ) and the rows are selected I tried it by this Function FilterOn() If ActiveSheet.FilterMode = True Then .. here should be "TRUE" Else .... here should be "FALSE" End If End Function Can someone help me ? -- Jamen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
detect ctrl-c was pressed (in copy mode) from vba? | Excel Programming | |||
detect ctrl-c was pressed (in copy mode) from vba? | Excel Programming | |||
How do I detect cell mode? | Excel Programming | |||
Detect event if excel is in edit mode. | Excel Programming | |||
Detect Group mode | Excel Programming |