Workbook_SheetCalculate Method

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Whenever the worksheet recalculates, I call a function that I have created.
However, the function apprars to run twice even though I am calling it once.
It the worksheet calcuating more than once? How can I fix this?

Thanks in advance for your help.
 
Is your code a UDF, or called from the worksheet calculate event ?
What does your function do ?

Tim
 
Calling the function is probably triggering a new recalc event and causing
the macro to rerun.

In your event code put the following at the beginning

Application.EnableEvents=False
This prevents events from triggering.

and then at the end turn back on the triggering.
Application.EnableEvents=True

If you crash or are debugging your event triggering may be left turned off
run a simple one liner to restore. (For some reason when I am testing event
triggered code I always forget this)

sub fixme()
Application.EnableEvents=true
End Sub
 
Thank you both for responding.

Paul, I was thinking the same thing that you were. When I stepped through
the debugging process, the second calculation actually doesn't happen it
tries to exit the on calculation sub routine.

When I used the code that you gave me, the function was actually called more
than twice.

Below is my code:

Here is the on calculate subroutine:
********************************************************
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Call Calculate

End Sub

********************************************************

and here is the subroutine that I am calling:

********************************************************
Sub Calculate()
Dim i As Integer
Dim a As Integer
Dim b As Integer
Dim intRecordCount As Integer

i = 2
a = 8
intRecordCount = ThisWorkbook.Sheets("Data").Cells(Rows.Count,
"A").End(xlUp).Row


Do While ThisWorkbook.Sheets("Daily Activity Summary").Cells(19, "I") <> ""
ThisWorkbook.Sheets("Daily Activity Summary").Rows(19).Delete
Loop

Do While ThisWorkbook.Sheets("Daily Activity Summary").Cells(21, "I") <> ""
ThisWorkbook.Sheets("Daily Activity Summary").Rows(21).Delete
Loop

Do While ThisWorkbook.Sheets("Daily Activity Summary").Cells(23, "I") <> ""
ThisWorkbook.Sheets("Daily Activity Summary").Rows(23).Delete
Loop

Do While a >= 4
Do While i <= intRecordCount
If ThisWorkbook.Sheets("Data").Cells(i, "A") = 1040 Then
If ThisWorkbook.Sheets("Data").Cells(i, "C") =
ThisWorkbook.Sheets("Daily Activity Summary").Cells(16, a) Then
If ThisWorkbook.Sheets("Data").Cells(i, "K") = False Then
ThisWorkbook.Sheets("Daily Activity
Summary").Rows(23).Insert
ThisWorkbook.Sheets("Daily Activity Summary").Cells(23,
"B") = ThisWorkbook.Sheets("Data").Cells(i, "F")
ThisWorkbook.Sheets("Daily Activity Summary").Cells(23,
a) = ThisWorkbook.Sheets("Data").Cells(i, "N")
ThisWorkbook.Sheets("Daily Activity Summary").Cells(23,
"I").Formula = "=SUM(" & ThisWorkbook.Sheets("Daily Activity
Summary").Range("D23:H23").Address & ")"
ThisWorkbook.Sheets("Daily Activity
Summary").Rows(23).Font.Bold = False

End If
End If
End If
i = i + 1
Loop
i = 2
a = a - 1
Loop

i = 2
a = 8

Do While a >= 4
Do While i <= intRecordCount
If ThisWorkbook.Sheets("Data").Cells(i, "A") = 1115 Then
If ThisWorkbook.Sheets("Data").Cells(i, "C") =
ThisWorkbook.Sheets("Daily Activity Summary").Cells(16, a) Then
If ThisWorkbook.Sheets("Data").Cells(i, "K") = False Then
ThisWorkbook.Sheets("Daily Activity
Summary").Rows(21).Insert
ThisWorkbook.Sheets("Daily Activity Summary").Cells(21,
"B") = ThisWorkbook.Sheets("Data").Cells(i, "F")
ThisWorkbook.Sheets("Daily Activity Summary").Cells(21,
a) = ThisWorkbook.Sheets("Data").Cells(i, "N")
ThisWorkbook.Sheets("Daily Activity Summary").Cells(21,
"I").Formula = "=SUM(" & ThisWorkbook.Sheets("Daily Activity
Summary").Range("D21:H21").Address & ")"
ThisWorkbook.Sheets("Daily Activity
Summary").Rows(21).Font.Bold = False

End If
End If
End If
i = i + 1
Loop
i = 2
a = a - 1
Loop

i = 2
a = 8

Do While a >= 4
Do While i <= intRecordCount
If ThisWorkbook.Sheets("Data").Cells(i, "A") = 2272 Then
If ThisWorkbook.Sheets("Data").Cells(i, "C") =
ThisWorkbook.Sheets("Daily Activity Summary").Cells(16, a) Then
If ThisWorkbook.Sheets("Data").Cells(i, "K") = False Then
ThisWorkbook.Sheets("Daily Activity
Summary").Rows(19).Insert
ThisWorkbook.Sheets("Daily Activity Summary").Cells(19,
"B") = ThisWorkbook.Sheets("Data").Cells(i, "F")
ThisWorkbook.Sheets("Daily Activity Summary").Cells(19,
a) = ThisWorkbook.Sheets("Data").Cells(i, "N")
ThisWorkbook.Sheets("Daily Activity Summary").Cells(19,
"I").Formula = "=SUM(" & ThisWorkbook.Sheets("Daily Activity
Summary").Range("D19:H19").Address & ")"
ThisWorkbook.Sheets("Daily Activity
Summary").Rows(19).Font.Bold = False

End If
End If
End If
i = i + 1
Loop
i = 2
a = a - 1
Loop


End Sub
********************************************************

Thanks again for your help.
 
Back
Top