I didn't get an #name? error when I tried your formula in xl2003.

But I bet Steve Bell isolated the problem because your sub puts in a formula
that needs to be calculated.

Since I didn't get the error, I'm not sure if this helps:

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Worksheets("Sheet1").Cells(2, 2).Formula = "=Sum(A1:A5)"
'or
'me.Cells(2,2).formula = "=sum(a1:a5")
Application.Calculate
Application.EnableEvents = True
End Sub

Is this event behind Sheet1 or a different worksheet?

If it's behind sheet1, then use the me.cells(...) line and delete line with
worksheets("sheet1").cells(...).

If it's behind a different sheet, then delete the me.cells(...) line. Notice
that I dropped the .activate line and worked directly on the range.

(Ps. I changed the .cells(2,1) to .cells(2,2) to avoid the circular reference
while testing. Change it to what you need.)



ciccia wrote:

From the Excel, I tried to use Visual Basic Edit to create some sub/functions.

Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Formula = "=Sum(A1:A5)"
End Sub

From the Sheet1, I enter "Worksheet_Calculate()" in a cell.
#Name? displays in the cell where I entered the sub name.
Why?

--

Dave Peterson