What was selected in autofilter
 

Hi, i want to know, how to achieve what was selected in autofilter in row 5
via VBA code and than trigger a code. Thanks.

Henrich

What was selected in autofilter
 

This function returns the filter value

Public Function ShowFilter(rng As Range)
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



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Henrich" wrote in message
...
Hi, i want to know, how to achieve what was selected in autofilter in row
5
via VBA code and than trigger a code. Thanks.

Henrich

What was selected in autofilter
 

Bob,

PERFECT JOB, but one more question. Why the result beginns with '=', like
=Name and not just only Name?

Thanks

Henrich

€žBob Phillips" napÃ*sal (napÃ*sala):

This function returns the filter value

Public Function ShowFilter(rng As Range)
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



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Henrich" wrote in message
...
Hi, i want to know, how to achieve what was selected in autofilter in row
5
via VBA code and than trigger a code. Thanks.

Henrich

What was selected in autofilter
 

because you could be filtering for equals, greater than, less than etc. so
it includes the operator.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Henrich" wrote in message
...
Bob,

PERFECT JOB, but one more question. Why the result beginns with '=', like
=Name and not just only Name?

Thanks

Henrich

"Bob Phillips" napísal (napísala):

This function returns the filter value

Public Function ShowFilter(rng As Range)
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



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Henrich" wrote in message
...
Hi, i want to know, how to achieve what was selected in autofilter in
row
5
via VBA code and than trigger a code. Thanks.

Henrich