Data validation bug?

[swaugh]

I have a simple data validation formula to check whether a valid number is entered or not. It works, provided that the user types the number, then presses either the ENTER or TAB key.

If they type a number, then click another cell using the mouse, the number will be accepted, with apparently no data validation check kicking in. In other words, it will accept an invalid number.

I have style STOP selected, I have IGNORE BLANK clicked on (though I don't think that even applies here).

Am I missing something really obvious here?

[JBeaucaire]

Must be a bug in your system, that doesn't work for me. You might want to verify that behavior in other workbooks. Post one here that lets you do that.

[swaugh]

Here's the workbook in question. Please look at the April2011 tab, cell Z17 - it has the data validation - it should not allow entry of a number - if you enter anumber then hit TAB or ENTER - you'll see the data validation message.
If you enter the number then immediately click in another adjacent cell, the number will be entered with no data validation.
Help please, and thanks for the response so far
Susan

[NBVC]

Try changing formula to:

=$B$44>0

[swaugh]

Thanks, but I'm specifically checking for a <0 value in $B$44 - in other words, they can't take hours they don't have in the "bank" (represented by value in cell B44). The issue isn't the formula, it works just fine - the issue is it only works if you press enter or tab, but not if you immediately click in another cell.

[Cutter]

The issue actually is your formula because the value in B44 is not less than 0, it is 0.
Changing your formula to:

Please Login or Register  to view this content.

solves the problem without defeating your intent.

But why wouldn't you be using:

Please Login or Register  to view this content.

Wouldn't that be a better way to do it?

[NBVC]

My formula:

=$B$44>0, which is theoretically the same as your =IF($B$44<=0,FALSE,TRUE)

It will not allow entry in Z17 unless B44>0

With data validation and conditional formatting, you should write the functions so that the result is TRUE if the condition is met. There is no need for an IF() formula... the result of B44>0 is TRUE or FALSE by default.

[romperstomper]

It's a bug in Excel. (it's not 2007 specific as I've heard of it before and it still occurs in 2010) I can't think of a way round it offhand due to the circular nature of the validation.

[NBVC]

When I applied the formula as =$B$44>0, it seemed to work, at least I think it did

[romperstomper]

Not for me. If you type a number in Z17 (without pressing enter or tab) and then select another cell with the mouse, it bypasses the validation completely. (the original formula wasn't the problem - it works fine if you use enter or tab)

[NBVC]

You're right. I tested it again, but my suggestion was a little different...

=$B$44>0 removes the (=0 condition, which I assume should also disqualify the entry)... and with that it works as desired. It's has to do with the =0 part not triggering when B44 actually equals 0, it seems.

[swaugh]

Thanks everybody for the responses - the issue truly isn't the formula (and a value of 0 is ok, it is only a <0 value that isn't), it's getting the formula to trigger when another cell is clicked, rather than using the enter or tab key after a value is entered. I think we'll just make sure instructions are clear to use enter or tab only.

[Kraglin1]

I realize this thread is over 2 years old at this point, but curious if anyone found a solution to the problem. I'm seeing the same thing in Excel 2007 and 2010 and would love to find a solution without having to add even more code to my Workbook_Change() event.

[JBeaucaire]

I still have no workaround other than the ws_change event. Sorry.

[ArviL]

Excel2000 doesn't have this bug, so it is a later 'improvement' from MS. I discovered this bug just today when applied data validation in Excel2007. The validation formula is like:
=COUNTIF($G:$G;$G2)<2


Arvi Laanemets

[leo black]

Hi,
I am attempting to workaround this error. I just wonder Jerry what you have in mind with the ws_change event.

In my case, I am using a user defined function via an Indirect cell reference to validate that a cell contains only characters from a defined alphabet. Everything works perfectly when the cell input is valid. (It doesn't matter whether I leave the cell with the enter key, arrow key or mouse click on another cell.) When the cell input is invalid, the ws_change event certainly picks up the new contents of the cell and data validation fires properly - UNLESS I leave the error cell by clicking on another cell. Then, the inccorrect data is accepted as if no validation is applied to the cell.

[arlu1201]

leo black,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.