Excel 2007 : Worksheet_Change

[ThomasHaller]

Hello folks,

In the code shown below I am trying to error check the date the user has entered against the list of imported dates, which works fine except when the last line is executed in the "Else" block - which is supposed to replace the incorrect entered date with the original default value.

It seems that re-assignment triggers the "Worksheet_Change" event again (?) and because the date is now "in range" again, the "Then" block gets executed!?!

Is that what is happening? Why else would I see the MsgBox "Date Entered in in Range." every time, after seeing the MsgBox "Error! X_axis..."?

Please Login or Register  to view this content.

So, here is an attempt to "out-wit" the VB logic, by having that Error flag set to 1 at the time of the re-assigment, but it doesn't work. I still see the logic passing through the "Then" block after the re-assignment in the "Else" block.

Please Login or Register  to view this content.

Any ideas how I can put a correct data value back in the cell after the user enters an invalid date? Without triggering the "Worksheet_Change" event? Help!

- Thomas

[royUK]

See if this works

Please Login or Register  to view this content.

[ThomasHaller]

Hello royUK,

I will try that, what I found myself was that second block of code works, if I declare the "Dim Error as Integer" outside of the If statement, as a "Static" variable. So here is the block that works fairly well:

Please Login or Register  to view this content.

But I will play with your suggestion, just to see how it works.

- Thomas

[ThomasHaller]

Hello again royUK,

Your solution works nicely and I like it much better than my messy "Error" flag!

SOLVED!

- Thomas