Data Validation Dont allow blanks

[JC2710]

Hi

I have a data validation list, but i dont want the user to be able to make the cell blank. I have 5 items in the list and i wont to make sure they choose one. But the data validation allows them to enter blank.

Any way round this? Ignore blank doesnt seem to do anything!

Thanks

 

[Yard]

Hi,

From the Excel Help:

1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
   Note If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

I think you need to have another condition somewhere which checks if the cell contains a value or not.

HTH

 

[Yard]

(That said, I can't get the Ignore blank option to work, even in the context described in the Help!)

:rolls eyes:

Better read it again......

 

[metropol]

When you marked your area for the list, I presume you didn't select a blank cell as well?

 

[JC2710]

No. I have 5 items in my list. No blanks in the list.

But if I dont select anything and leave the cell blank i dont get an error.

 

[metropol]

No. I have 5 items in my list. No blanks in the list.

But if I dont select anything and leave the cell blank i dont get an error.

that's correct. The datavalidation only act on your selection. When you initial create the validation list, it will occur with a blank the first time. Then you have to do your first selection.

It makes sense, because if you use autofill it would create hundres of error messages.
Ex you enter a datavalitdation list in A1. Then you autofill to A100. If it should give you an error message for all these cells, it would be conflicts. Then again, Excel can't suggest a value either since it doesn't know which one of the values in the list to pick.

I can only see this work with use of conditional formatting. "If the value is 0 then pattern Red".

Or you could use a macro to run through the validationlists and highlight the cells with blanks

 

[Yard]

No. I have 5 items in my list. No blanks in the list.

But if I dont select anything and leave the cell blank i dont get an error.

Well, that makes sense - how does Excel know you've "left" it blank, i.e. how long after opening the workbook do you want it to wait before you got an error?!

It would be good if it had the option to disallow the entry being deleted, but that isn't the case (without VBA).

Options include:

• Conditional Formatting to highlight if the cell is empty.
• IF statment in whatever formula depends on this cell entry, so that if no entry is made, no result is displayed (or whatever).
• VBA code to prevent the cell being emptied or a message box popping up on a certain event to (annoyingly) remind the user to make an entry

HTH

 

[JC2710]

Ok thanks guys

I went with Yards suggestion of conditional formatting if the cell is blank.

Thanks

 

[metropol]

Ok thanks guys

I went with Yards suggestion of conditional formatting if the cell is blank.

Thanks

Pretty much the same.

 

[Peter_SSs]

JC2710

Not sure if this will do exactly what you want but you could give it a try. I suggest testing on a copy of your workbook.

1. Right click the sheet name tab and choose "View Code".

2. Copy the code below and Paste into the main right hand pane that opens at step 1.

3. Adjust the "E5" in the first line to match the address of your Data Validation cell.

4. Close the the VB window.

5. Test.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> DVcell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E5"<br><SPAN style="color:#00007F">Private</SPAN> DVcellSelected <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> DVcellSelected <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> IsEmpty(Range(DVcell)) <SPAN style="color:#00007F">Then</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            Range(DVcell).Select<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            MsgBox "You must enter a value in cell " & DVcell<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range(DVcell)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        DVcellSelected = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        DVcellSelected = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>