Almost there Michael...........
This one will give a "pass" if ANY column is filtered on, and will return a
blank window if the column is not "B". Somehow if we could take that return
and allow a "pass" only if the return was NOT blank.
Vaya con Dios,
Chuck, CABGx3
"Michael" wrote:
OK, modify my sub and make sure you also copy the function.
Function courtesy of http://www.ozgrid.com/VBA/autofilter-criteria.htm
Sub IsFilteron()
Dim iLastRow As Double
Dim numCells As Double
iLastRow = Range("B65536").End(xlUp).Row
numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count
If numCells < iLastRow Then ' This determines if there is a filter on B
strFltrCrit = AutoFilter_Criteria(Range("B1")) 'This returns the
criteria used on the filter
MsgBox (strFltrCrit)
'Call yourmacro
End If
End Sub
Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String
Application.Volatile
With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then Exit Function
strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If
End With
End With
AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function
--
If this posting was helpful, please click on the Yes button.
Regards,
Michael Arch.
"CLR" wrote:
Thanks Michael, but I guess I wasn't clear in my post. I only want my macro
to run IF the Autofilter is on for Column B, AND a selection has been made in
Column B. Column B is a list of names, and I only want to run the macro
against ONE SELECTED name.
How can I modify your code to do that?
Vaya Con Dios,
Chuck, CABGx3
"Michael" wrote:
You can modify this instruction to be run on one or multiple columns; as is,
it will check in the whole sheet.
If Worksheets("Sheet1").AutoFilterMode Then
isOn = "On"
Else
isOn = "Off"
End If
MsgBox "AutoFilterMode is " & isOn
--
If this posting was helpful, please click on the Yes button.
Regards,
Michael Arch.
"CLR" wrote:
Hi All.....
I have a macro that I would like to first check if the sheet1 has been
Autofiltered for any selection in Column B before it will continue to
run.......the sheet may or may not be also Autofiltered for other columns,
but it must also be Autofiltered for a selection in column B to qualify.
Any help would be much appreciated.
TIA
Vaya con Dios,
Chuck, CABGx3