Worksheet_Change Event 1

[mavest]

How best can I escape an endless (infinte) series of worksheet_change events? Is there and FAQ, or does anyone have any suggestions? Thanks!

Details:
I am using the worksheet change event to trigger a calculation when a user types in a value in a certain cell. However, as the last step of the calculation I would like to update the value typed by the user, i.e. trigger cell. When this is done it causes the change event to occur again, and as you can see this will cause an endless (infinite) loop of change events.

 

[1DMF]

if the data typed by the user is not the result , why are you putting the result in the place where the user typed the data.

shouldn't the result of the calculation be placed in a 'total' type cell seperate from what they typed.

Just thinking out loud here not knowing exactly what your sheet looks like or what you're doing.

or can you set a counter field, when they type the event sets a counter to +1, the loop checks for the field to = 1 and runs the calc, the final part of the calc sets the counter to +1 then updates the field, because the field now = 2 the loop doesn't do the calc but instead sets the counter back to 0

hope this makes sense.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[SkipVought]

Hi,

Application.enableevents=false   'turn events off
'...do stuff that would normally trigger the event

Application.enableevents=true   'finally turn events back on
end sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[1DMF]

that sounds like a plan skip

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[mavest]

Great! Thanks Skip!

I was hoping that there was an enable events toggle out there!!

Other Solution:
In the past I've used the counter/boolean idea, by declaring a global and changing its value inside the event. But this solution is messy and if not properly implemented can lead to uncertain results.

Further:
In my case the cell changed by the user is the "set Point" for an iterative solution. Depending on how closely the solution converges upon the set point, I'd like to "update" the set point.

 

[SkipVought]

I'd personally use one cell for user input separate from the cell you would update programatically.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}