Workbook_SheetCalculate looping
 

I have a macro that I need to run when ever a calculation is performe
on the activesheet.

I am using the following code, in the workbook object to carry tha
out:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

With ActiveSheet

SheetNumberUpdate

End With

End Sub

It does fire when the calculation occurs and runs the macro, howeve
once it continues to run cycling back through the macro. Lather
rinse, repeat!

I tried placing the above code in the module with the macro but then i
doesnt work at all.

Help!!!!!!!!!

--
Hammer_75
-----------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...nfo&userid=741
View this thread: http://www.excelforum.com/showthread.php?threadid=40046

Workbook_SheetCalculate looping
 

You have a recursive loop. You should be able to fix it with

application.enableevents

AS with all application level settiing it should have an error handler to
deal with the potential of a crash

sub Whatever()
on error goto ErrorHandler
application.enableevents = false
'do your stuff

Errorhandler:
application.enableevents = true
end sub
--
HTH...

Jim Thomlinson


"Hammer_757" wrote:


I have a macro that I need to run when ever a calculation is performed
on the activesheet.

I am using the following code, in the workbook object to carry that
out:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

With ActiveSheet

SheetNumberUpdate

End With

End Sub

It does fire when the calculation occurs and runs the macro, however
once it continues to run cycling back through the macro. Lather,
rinse, repeat!

I tried placing the above code in the module with the macro but then it
doesnt work at all.

Help!!!!!!!!!!


--
Hammer_757
------------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...fo&userid=7413
View this thread: http://www.excelforum.com/showthread...hreadid=400466

Workbook_SheetCalculate looping
 

Thanks Jim, that works.

the education continue

--
Hammer_75
-----------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...nfo&userid=741
View this thread: http://www.excelforum.com/showthread.php?threadid=40046