I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
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
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin

An Additional pieces of data.

For the cells that invoke the FillAcctCode macro, if I select the data from
a defined dropdown list, the error occurs. If I type in the very same value,
the error does NOT occur. If I delete the contents of the cell, the error
does not occur.

The NotFiltered was leveraged from another thread here from Mike H and
Gary's Student.
Question: Why is the return type String instead of Boolean?

Thanks,
John

"DocBrown" wrote:

I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
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
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin

in here
Sub FillAcctCode(ByVal Target As Range)

near where you marked the error you haev a second ELSE statement

your syntax should be either
IF .... ELSE ... END IF
or
IF .... ELSEIF [.....ELSEIF] ...[ELSE]...END IF

you can't have more than one ELSE



"DocBrown" wrote:

I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
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
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin

Thanks for your input. My code fragment may not have been clear. That
FillAcctCode function works correctly when the IsFiltered function is not
referenced in any cell. Here's the whole code section:

For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then
colCategory = Range("Bud_CatSubCatCols").Column
colAccount = Range("Bud_AcctCodeCol").Column

' If the character in the override column isn't the same as char in
header, then fill in the account code.
If ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value < _
ActiveSheet.Cells(Range("Bud_AcctCodeCol").Row - 1, colAccount -
1).Value Then

' If the Category or Subcategory is empty then clear the account code,
' else fill in account code and put space in override column.
If Cells(rngCurrRow.Row, colCategory) = "" Or Cells(rngCurrRow.Row,
colCategory + 1) = "" Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
Else
' Put a char in override colum so Item Description doesn't bleed
into column.
ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value = " "

' If we got a valid account code, fill in account code 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
End If

Else
' The Override is set, but check if the user has set category and
subcategory
' that matches the account code.
Set rngCurrAcct = Range(Cells(rngCurrRow.Row, colAccount),
Cells(rngCurrRow.Row, colAccount))
SetOverRide rngCurrAcct
End If

Else
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)

If Len(Trim(strAcctCode)) 0 Then
' Fails at next line.
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
End If
Next rngCurrRow

It seems that as soon as I put the IsFiltered function in any cell formula,
then this line following line in the FillAcctCode generates the 1004 error:

ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode

Of course this code may be fine but some other condition in Excel is being
set that causes it to fail. Just for grins sake I put the following formula
in an otherwise blank unused cell. And I still got the failure.
=IsFiltered(range("$c$30:$Q$159"))

The IsFiltered() function seems to be doing what it's suppose to do. I'm
going to tear apart the IsFiltered function to see if I can narrow down the
trigger.

Any feedback is appreciated.

John.

"Patrick Molloy" wrote:

in here
Sub FillAcctCode(ByVal Target As Range)

near where you marked the error you haev a second ELSE statement

your syntax should be either
IF .... ELSE ... END IF
or
IF .... ELSEIF [.....ELSEIF] ...[ELSE]...END IF

you can't have more than one ELSE



"DocBrown" wrote:

I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
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
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin

John
It sounds like you cannot check the sheet filtered state from within a
cell function.
There are lots of things you may not do inside a UDF when called from a
cell, that are just fine within pure VBA.

I can't just find a list, but if it works when triggered from VBA but
not in a cell, then its likely to be a blocked item.

The easiest way around it is to run the VBA as a command rather than an
in cell function.

Cheers
Simon
Excel development website: www.codematic.net


DocBrown wrote:
Thanks for your input. My code fragment may not have been clear. That
FillAcctCode function works correctly when the IsFiltered function is not
referenced in any cell. Here's the whole code section:

For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then
colCategory = Range("Bud_CatSubCatCols").Column
colAccount = Range("Bud_AcctCodeCol").Column

' If the character in the override column isn't the same as char in
header, then fill in the account code.
If ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value < _
ActiveSheet.Cells(Range("Bud_AcctCodeCol").Row - 1, colAccount -
1).Value Then

' If the Category or Subcategory is empty then clear the account code,
' else fill in account code and put space in override column.
If Cells(rngCurrRow.Row, colCategory) = "" Or Cells(rngCurrRow.Row,
colCategory + 1) = "" Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
Else
' Put a char in override colum so Item Description doesn't bleed
into column.
ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value = " "

' If we got a valid account code, fill in account code 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
End If

Else
' The Override is set, but check if the user has set category and
subcategory
' that matches the account code.
Set rngCurrAcct = Range(Cells(rngCurrRow.Row, colAccount),
Cells(rngCurrRow.Row, colAccount))
SetOverRide rngCurrAcct
End If

Else
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)

If Len(Trim(strAcctCode)) 0 Then
' Fails at next line.
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
End If
Next rngCurrRow

It seems that as soon as I put the IsFiltered function in any cell formula,
then this line following line in the FillAcctCode generates the 1004 error:

ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode

Of course this code may be fine but some other condition in Excel is being
set that causes it to fail. Just for grins sake I put the following formula
in an otherwise blank unused cell. And I still got the failure.
=IsFiltered(range("$c$30:$Q$159"))

The IsFiltered() function seems to be doing what it's suppose to do. I'm
going to tear apart the IsFiltered function to see if I can narrow down the
trigger.

Any feedback is appreciated.

John.

"Patrick Molloy" wrote:

in here
Sub FillAcctCode(ByVal Target As Range)

near where you marked the error you haev a second ELSE statement

your syntax should be either
IF .... ELSE ... END IF
or
IF .... ELSEIF [.....ELSEIF] ...[ELSE]...END IF

you can't have more than one ELSE



"DocBrown" wrote:

I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
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
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin

I stripped out almost everything from the IsFiltered function and the error
occurs when I add a statement referencing a cell on the WS.

The most telling test came when I ran the WB on Excel 2003 on my system at
work. IT WORKED! I am using Excel 2002 (Office XP) on my system at home. So
apparently the issue is fixed in newer versions. Haven't tried 2007 yet.

Given that I still need pre-2007 to test on, I wish I had a workaround. I
doubt I can get a 2003 version of Office for a resonable price, given that is
almost obsolete also.

John

"Simon Murphy" wrote:

John
It sounds like you cannot check the sheet filtered state from within a
cell function.
There are lots of things you may not do inside a UDF when called from a
cell, that are just fine within pure VBA.

I can't just find a list, but if it works when triggered from VBA but
not in a cell, then its likely to be a blocked item.

The easiest way around it is to run the VBA as a command rather than an
in cell function.

Cheers
Simon
Excel development website: www.codematic.net


DocBrown wrote:
Thanks for your input. My code fragment may not have been clear. That
FillAcctCode function works correctly when the IsFiltered function is not
referenced in any cell. Here's the whole code section:

For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then
colCategory = Range("Bud_CatSubCatCols").Column
colAccount = Range("Bud_AcctCodeCol").Column

' If the character in the override column isn't the same as char in
header, then fill in the account code.
If ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value < _
ActiveSheet.Cells(Range("Bud_AcctCodeCol").Row - 1, colAccount -
1).Value Then

' If the Category or Subcategory is empty then clear the account code,
' else fill in account code and put space in override column.
If Cells(rngCurrRow.Row, colCategory) = "" Or Cells(rngCurrRow.Row,
colCategory + 1) = "" Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
Else
' Put a char in override colum so Item Description doesn't bleed
into column.
ActiveSheet.Cells(rngCurrRow.Row, colAccount - 1).Value = " "

' If we got a valid account code, fill in account code 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
End If

Else
' The Override is set, but check if the user has set category and
subcategory
' that matches the account code.
Set rngCurrAcct = Range(Cells(rngCurrRow.Row, colAccount),
Cells(rngCurrRow.Row, colAccount))
SetOverRide rngCurrAcct
End If

Else
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)

If Len(Trim(strAcctCode)) 0 Then
' Fails at next line.
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
End If
Next rngCurrRow

It seems that as soon as I put the IsFiltered function in any cell formula,
then this line following line in the FillAcctCode generates the 1004 error:

ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode

Of course this code may be fine but some other condition in Excel is being
set that causes it to fail. Just for grins sake I put the following formula
in an otherwise blank unused cell. And I still got the failure.
=IsFiltered(range("$c$30:$Q$159"))

The IsFiltered() function seems to be doing what it's suppose to do. I'm
going to tear apart the IsFiltered function to see if I can narrow down the
trigger.

Any feedback is appreciated.

John.

"Patrick Molloy" wrote:

in here
Sub FillAcctCode(ByVal Target As Range)

near where you marked the error you haev a second ELSE statement

your syntax should be either
IF .... ELSE ... END IF
or
IF .... ELSEIF [.....ELSEIF] ...[ELSE]...END IF

you can't have more than one ELSE



"DocBrown" wrote:

I hope someone can redirect me before my head gets too bloody from beating it
against the wall.

I have a UDF that's intended to determine if an AutoFilter is active on a
portion of my worksheet. I got the basic idea from another posting here. This
UDF is referenced in cell in another portion of the WS. The intention is to
blank a cell if an AutoFilter is On.

I have another Macro invoked by the Worksheet_Change event that will try to
fill in cells in rows based on the current selected cells and other lists in
another worksheet.

The error is 1004 - Application-defined or object-defined error. It occurs
when I modify one of the cells that invokes function below called
FillAcctCode().

The weird part is that the failure only occurs if two workbooks are opened
at the same time. The two workbooks have most of the same macros. (one is a
modified version of the other where I'm trying to get this AutoFilter stuff
working.)

Trying to avoid putting too much code here, here's what I think are the
relevant parts:

The cell macro:
=IF(AND(NotFiltered($C$29:$Q$159),OR(F13<"",G13 < ""),H13< ""),H13-I13,"")

The UDF:

Private Function NotFiltered(MyRange As Range) As String
Application.Volatile
Dim i As Integer
NotFiltered = True
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
NotFiltered = False
End If
End With
Next
End With

End Function


The other macro code that hits the error:

Sub FillAcctCode(ByVal Target As Range)
'================================================= =====================
' By: John Spitzer
' Date: 05/15/2009

Dim rngCatSubcat As Range
Dim rngCurrRow As Range
Dim rngCurrAcct As Range
Dim colCategory As Long
Dim colAccount As Long
Dim strAcctCode As String

On Error GoTo ErrThisSub
If Target.Row = Range("Bud_ExpenditureTable").Row Then
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_CatSubCatCols"))
Else
Set rngCatSubcat = Application.Intersect(Target,
Range("Bud_AllocationTable"))
End If
' Loop through all the selected rows and
' if the category or subcategory columns are empty clear the account code
' else fill in the account code.
For Each rngCurrRow In rngCatSubcat
If Target.Row = Range("Bud_ExpenditureTable").Row Then

' process rows in the main table.

else
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
'<-- fails here
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = "" '<-- or
fails here
End If
End If
Next rngCurrRow

Does anyone have an idea where to look for the problem?

Thanks a bunch!
Johin

I think I may be on to what may be the cause of this error, but I'm not sure.
I think that I'm hitting the issue where UDFs are not allowed to modify other
cells in the WS. It appears that when I actually have a UDF called from a
cell, it sets up the condition that my other function that is invoked by the
Worksheet_change event is no longer allowed to alter cells. I'm not sure what
cells it would be allowed/not allowed to change.

it might be some how related to the order the functions are called when the
WS recalculates or some other condition.

I changed the WS_change called routine so it sets public global vars and had
one set of cells that I changing get the global values and the error didn't
happen.

From my debug.print statements, I don't see why the WS_change invoked
routine should get caught up in the UDF limitation, or why the WS_change
routine is allowed to alter cells when there is no UDF on the WS.

Any ideas for a workaround? Any ideas how to allow my WS_change routine to
alter cells?

Thanks.