Hi OssieMac,

Thanks for that Function. The formula works great.

Unfortunately, it triggers the same problem as my function. The issue has to
do with that pesky Application.Volatile statement. Here is a portion of the
macro that's hitting the RT Error 1004:


colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)
If Len(Trim(strAcctCode)) 0 Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If

This code runs perfect if I comment out that Volatile statement but fails at
the
Activesheet... statement if that Volatile statement is in. Or if the formula
=MyFilters() is on the worksheet. The Volatile is required otherwise the
function won't run when needed.

I've researched and found information that says that User Defined Functions
aren't allowed to alter cells other than the cell the function is in. Well,
my macro is doing just that, but Execl is ok with that EXCEPT when I have a
function with Volatile being executed. My functions are being run by the
Worksheet_change event. It also seems to occur on Exel 2002, but not newer
versions.

Now if someone can tell me what up with that or a way to workaround it in
this case, I'd be a very happy camper.

John


"OssieMac" wrote:

Hi again John,

Just for some added interest, lets say you have multiple worksheets with
autofilter and you want to use the User Defined function on each worksheet
then you could use the Caller.Parent as the worksheet. The method I gave you
only ever tests Sheet1 irrespective of the worksheet you place the formula
in. The following method will always test the worksheet that contains the
function.

Function MyFilters()

Application.Volatile

MyFilters = False
With Application.Caller.Parent
If .AutoFilterMode Then
If .FilterMode Then
MyFilters = True
End If
End If
End With

End Function

--
Regards,

OssieMac