Data Validation does not work when selecting another cell.
 

I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry.
However, if an invalid entry is typed in and the another cell is selected
with the mouse (without pressing Enter), the invalid data is accepted in the
cell. Is there any way of stopping this ?
Thanks
Simon

It worked for me when I tried it.

You may want to post more details or try it again.

Simon Jefford wrote:

I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry.
However, if an invalid entry is typed in and the another cell is selected
with the mouse (without pressing Enter), the invalid data is accepted in the
cell. Is there any way of stopping this ?
Thanks
Simon

--

Dave Peterson

I've never seen that behaviour. What validation is set on the cell? What
version of Excel are you using.

Simon Jefford wrote:
I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry.
However, if an invalid entry is typed in and the another cell is selected
with the mouse (without pressing Enter), the invalid data is accepted in the
cell. Is there any way of stopping this ?
Thanks
Simon


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra

Thanks for the reply.

I have recreated the error in the following example.
For instance, in a blank worksheet, put the formula "+b1" in cell a1. In
cell B1, the validation should be Custom and the formula being =A1=0. This
implies that the entry into cell B1 should not cause cell A1 to go nagative.
If you enter -1 in cell B1, it correctly produces a validation error.
However, if you sellect cell B1 and type -1 without pressing enter but
instead just select another cell on the worksheet, the entry is accepted.
Note, to cause this strange error, cell B1 has to be empty before typing -1
and selecting another cell.

I am using Excel 2002.

Thanks for your help.
Simon


"Debra Dalgleish" wrote:

I've never seen that behaviour. What validation is set on the cell? What
version of Excel are you using.

Simon Jefford wrote:
I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry.
However, if an invalid entry is typed in and the another cell is selected
with the mouse (without pressing Enter), the invalid data is accepted in the
cell. Is there any way of stopping this ?
Thanks
Simon


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Dave

Thanks for the reply.

I have recreated the error in the following example.
For instance, in a blank worksheet, put the formula "+b1" in cell a1. In
cell B1, the validation should be Custom and the formula being =A1=0. This
implies that the entry into cell B1 should not cause cell A1 to go nagative.
If you enter -1 in cell B1, it correctly produces a validation error.
However, if you sellect cell B1 and type -1 without pressing enter but
instead just select another cell on the worksheet, the entry is accepted.
Note, to cause this strange error, cell B1 has to be empty before typing -1
and selecting another cell.

I am using Excel 2002.

Thanks for your help.
Simon



"Dave Peterson" wrote:

It worked for me when I tried it.

You may want to post more details or try it again.

Simon Jefford wrote:

I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry.
However, if an invalid entry is typed in and the another cell is selected
with the mouse (without pressing Enter), the invalid data is accepted in the
cell. Is there any way of stopping this ?
Thanks
Simon

--

Dave Peterson

Well, I can recreate that, but don't know why it happens. Perhaps the
cross referencing is confusing Excel.

You could change the data validation in B1 to Decimal, where B1 is = 0,
and that should prevent the problem from occurring.

Simon Jefford wrote:
Debra

Thanks for the reply.

I have recreated the error in the following example.
For instance, in a blank worksheet, put the formula "+b1" in cell a1. In
cell B1, the validation should be Custom and the formula being =A1=0. This
implies that the entry into cell B1 should not cause cell A1 to go nagative.
If you enter -1 in cell B1, it correctly produces a validation error.
However, if you sellect cell B1 and type -1 without pressing enter but
instead just select another cell on the worksheet, the entry is accepted.
Note, to cause this strange error, cell B1 has to be empty before typing -1
and selecting another cell.

I am using Excel 2002.

Thanks for your help.
Simon


"Debra Dalgleish" wrote:


I've never seen that behaviour. What validation is set on the cell? What
version of Excel are you using.

Simon Jefford wrote:

I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry.
However, if an invalid entry is typed in and the another cell is selected
with the mouse (without pressing Enter), the invalid data is accepted in the
cell. Is there any way of stopping this ?
Thanks
Simon


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html