Not sure if anyone's good enough to figure this out, but I have difficulty with a Worksheet_Change event procedure.

I did some research but didn’t find any satisfying answer.

Basically the following procedure runs fine by itself, but throws an error if a UDF is present. It appears that the UDF event triggers before the Worksheet_Change event, thus causing an error if target is a range instead of an individual cells. But I don't know if that's the cause for the error.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Err_End
Dim uCell As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Target.Cells.Count > 1 Then
For Each uCell In Target
Cells(uCell.Row, 4) = 1
Cells(uCell.Row, 5) = 1
Next
End If

Err_Resume:
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Exit Sub

Err_End:
MsgBox Err.Description
Resume Err_Resume

End Sub


Now put this UDF in a module; copy one cell to a range of cells that is part of the UDF range argument.

This function sums the cell in the range argument if the row is visible e.g. =sumvisible(A1:A10)


Function SUMIFVISIBLE(MyRange As Range) As Single
On Error GoTo Err_SUMIFVISIBLE

Dim Rng As Range
Dim DaTotal As Single

DaTotal = 0

For Each Rng In MyRange
If Rng.EntireRow.Hidden = False Then
DaTotal = DaTotal + Rng.Value
End If

Next Rng

SUMIFVISIBLE = DaTotal
Exit Function



Err_SUMIFVISIBLE:
MsgBox Err.Description
End Function

Does any one have any idea what’s the problem? Boy would I ever appreciate to know the answer to that.

Thanks