Prevent null values in Excel data entry?

stuckfly

New Member
Joined
Mar 10, 2009
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
A funny thing happened on the way to the validation formula...

I have a custom validation rule to prevent a bunch of data entry cases, one of which is leaving a cell in a new record blank or deleting the cell value in an existing record. The null value assessment is ignored. Now, I understand doing nothing to a cell wouldn't provoke a validation rule, but apparently selecting a cell or deleting its contents won't either. Here's what I mean...

Starting with an empty cell, and using a formula to verify it's null indeed (a1="", isblank(a1), countblank(a1)...however you want to do it), I use a validation rule saying a null value is false.

I've already said the rule will not evaluate the cell until something is entered into it.

Selecting the cell and hitting Return won't work.

Deleting the cell's contents won't work.

But, double-clicking the cell or clicking in the formula bar as if to enter data, then hitting return will provoke the rule.

Of course, pasting a null value from elsewhere will delete the validation rule for the cell because the copied cell has no such rule. And, pasting a null value using paste-special-value is a handy way to circumvent any validation rule.

I'm posting this for two reasons:

1. Anyone else, without skills or time to make a form using VBA or XML or whatever, trying to find a way to force non-null data entry might find this information useful. I couldn't find anything about it, and I didn't think it was entirely obvious. Best I could design is, I put a comment on the cell and applied conditional formatting that highlights it if it's null.

2. It puzzles me that deleting the cell's contents does not provoke the validation rule, so I'm curious if anyone can explain that.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top