Why my procedure will re execute at a certain point in the procedu

  • Thread starter Thread starter lwchapman
  • Start date Start date
L

lwchapman

I have several routines (one in a module, the other in "sub
worksheet_calculate()."

In both, the code will execute until it reaches an undetermined line number,
then it will go back to the start of the procedure, run the commands and then
return to the next line from where it re executed. It will do this until it
has finally completed all the commands in the routine.

It may be that in the "sub worksheet_calculate()" procedure (in the
spreedsheet code page) that it writes to the worksheet which triggers a re
calculation and then returns to the beginning of the routine. If so, why
then would it return to the next line after the trigger command? In the
module routine this is not true. It is called from another routine in a User
Form. I have tried trapping it, work arounds, etc but nothing corrects this
problem. I am at a total loss!

Would anyone have any idea of what is occuring, or has had a similar problem
with a resolution?

Your advice, comments or suggestions would greatly be appreciated.

I use XP pro and Excel - Office 2007.

Regards - Larry
 
Try this:

Private Sub Worksheet_Calculate()

Dim ShName As String
Dim Grid As String

On Error GoTo ws_exit
Application.EnableEvents = False

your code

ws_exit:
Application.EnableEvents = True

End Sub
 
Hello Karen53,

You wrote:
*******************************************
Try this:

Private Sub Worksheet_Calculate()

Dim ShName As String
Dim Grid As String

On Error GoTo ws_exit
Application.EnableEvents = False

your code

ws_exit:
Application.EnableEvents = True

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

It appears that this has resolved my problem. Many, many thanks for your
great help!

I am a bit confused about the two variable strings that you created (ShName
& Grid)?

They do not appear any where in "your code"? How are they necessary to the
solution that you so graciously provided?

Thanking you again for your help.

Regards - Larry
 
Hi Larry,

I'm glad I could help. Sorry for the confusion. I pasted one of my
procedures and deleted code lines. I just missed those two. I usually put
my dim declarations before the 'on error goto', but you don't have to. You
could start with on error.
 
Back
Top