data validation - banning blank entries

[rudekid]

this is driving me nuts, probably because it should be straightforward.

Am trying to set up a company wide data entry spreadsheet that matche
the requirements of an oracle database, I therefore have several field
which require different data boundaries, e.g. character lengths, types
mandatory vs optional, etc.

But...

Can't get it to give an error message for the mandatory fields A
ALL!!

Have tried using validation for values greater than 0. Also tried
custom function of =not(isblank(I2)). I can't use a list function a
all I'm trying to do is make sure the field(s) is/are populated.

I know I can set up a macro that scans the document for blank entrie
but I'd rather the prompt were given at input rather than e.g. save.
Any ideas????

t

 

[Paul]

this is driving me nuts, probably because it should be straightforward.

Am trying to set up a company wide data entry spreadsheet that matches
the requirements of an oracle database, I therefore have several fields
which require different data boundaries, e.g. character lengths, types,
mandatory vs optional, etc.

But...

Can't get it to give an error message for the mandatory fields AT
ALL!!

Have tried using validation for values greater than 0. Also tried a
custom function of =not(isblank(I2)). I can't use a list function as
all I'm trying to do is make sure the field(s) is/are populated.

I know I can set up a macro that scans the document for blank entries
but I'd rather the prompt were given at input rather than e.g. save.
Any ideas????

On the Data Validation pop-up, uncheck the "Ignore blank" box.

 

[rudekid]

ah, if only life were that simple: tried that, still doesn't do anythin

 

[Peo Sjoblom]

You can't do that with validation, it's one thing to check what users type
into a cell,
another thing to force them to type into a cell. You could use VBA I suppose
but if they
disable macros when they open the file it won't do any good..

 

[Paul]

ah, if only life were that simple: tried that, still doesn't do anything

Well, it does do something! If you try to delete the contents of a cell
(i.e. leave it blank), you will get the error pop-up. I accept that this
only occurs once you are editing the cell; if you never go near it, you
won't see the pop-up. I thinks that's the best you can get with data
validation. Otherwise, you're looking at a macro.

 

[rudekid]

okay, thanks all.

Thinking about it, it's entirely logical why you couldn't do that as
in theory it would give you a constant error message the second yo
opened the spreadsheet

 

[Dave Peterson]

I think I'd try to use an adjacent cell (formatted Bold/Red) that contained:
=if(a1<>"","","Please fill in A1!"

If you really wanted to get industrious, you could use the workbook_beforesave
event that checked to see if all the cells are filled in:

I named a bunch of cells on Sheet1: MustBeFilled

Then I put this code behind the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("mustbefilled")

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Noooooo! You must complete the form before you save!"
Cancel = True
End If

End Sub

But this only gets caught when the user tries to save. (I'd find it
irritating--I may want to save before I'm done entering data.)

And this fails if the user disables macros, too (or just disables events).

And since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm