worksheet_change event fires multiple times
 

I have a cell formated as "h:mm AM/PM". If the user doesn't enter
valid time, they get an error message. If they don't enter a vali
time again, they keep getting the error message until the press Cancel
or enter a valid time.

I'm keeping track of the changes on the spreadsheet using th
worksheet_change event.

When the user enters a valid time the worksheet_change event fires.

If the user had entered an invalid time 2 times, then finally enters
valid time, the worksheet_change event fires 3 times.

Is there an event that would only fire when the user has entered
valid time, and only fire once

--
timconsta
-----------------------------------------------------------------------
timconstan's Profile: http://www.excelforum.com/member.php...fo&userid=1503
View this thread: http://www.excelforum.com/showthread.php?threadid=26650

worksheet_change event fires multiple times
 

If your change event is firing multiple time then your on change event must
be making a change itself. (recursive call) To fix this at the beginning of
the on change event add this

application.enableevents = false

and at the end

application.enableevents = true

when you use code like this it is a good idea to include error handling
which turns events back on in case of an error

Top of sub

On Error Goto ErrorHandler

Bottom of sub

exit sub
ErrorHandler:
application.enableevents = true
application.screenupdating = true
end sub

Hope this helps...

"timconstan" wrote:


I have a cell formated as "h:mm AM/PM". If the user doesn't enter a
valid time, they get an error message. If they don't enter a valid
time again, they keep getting the error message until the press Cancel,
or enter a valid time.

I'm keeping track of the changes on the spreadsheet using the
worksheet_change event.

When the user enters a valid time the worksheet_change event fires.

If the user had entered an invalid time 2 times, then finally enters a
valid time, the worksheet_change event fires 3 times.

Is there an event that would only fire when the user has entered a
valid time, and only fire once?


--
timconstan
------------------------------------------------------------------------
timconstan's Profile: http://www.excelforum.com/member.php...o&userid=15036
View this thread: http://www.excelforum.com/showthread...hreadid=266509