Excel Validation 1

[bwesenberg]

I am using Excel 2002.
I know this can be done; I am just drawing a blank.
I have a template that is locked down and the users start a new spreadsheet based on the template. The fields start out as blank and they will tab through each open cell and enter information. There are 4 cells that we want to make required that would have to have a number in them and it can be 0 or any other whole number. However, it must have a number in it.
I have been working with the data validation and cannot seem to get it to work.
Here are some of the things that I have tried:
Data validation for whole number with the data greater than or equal to 0 and unchecking the Ignore blank.

Custom with the formula =ISTEXT(J22) J22 being the cell I am in. Unchecking the Ignore blank.

Custom with the formula =ISNUMBER(J22). Unchecking the Ignore blank.

After I make a setting change to try something different, I lock down the template. Save it and then start a new spreadsheet based on the template.

With all of the things I have tried I can always tab past the boxes with the validation on them.

I have set the Input Message and Error Alert message and when I am in the cell the Input message works just fine, but then I can tab right past the cell and never get the error message.

I have searched the 3 Excel books I have and tried all their solutions and even searched the MS Excel Help online and off.

HELP!! I am just drawing a blank. I know this can be done and is easy.
I have to be doing just one small thing wrong, I just can't figure it out.

Any insight would be so helpful!!

 

[Zathras]

One thing that comes to mind, select a large block of cells and apply conditional formatting such that the background becomes solid [red] RED [/red] with this formula:

=COUNTA($A$1:$D$1)<4

where A1 thru D1 are the cells you need to have data in. It won't force an entry in all of those cells, but it should encourage it.

 

[SkipVought]

Hi,

Blank cells will always pass the validation test. I would add some code to test for the blank on a change in the validation range.

Copy 'n' Paste this code in the SHEET OBJECT - right click the sheet tab and select View Code. be sure to set your validation range as annotated below

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range, val As Range
'''SET YOUR VALIDATION RANGE
   Set val = Range("A9:A14")
   With Target
      If .Count > 1 Then Exit Sub
      Application.EnableEvents = False
      Set rng = Application.Intersect(Target, val)
      If Not rng Is Nothing Then
         If .Value = "" Then .Value = 0
      End If
   End With
   
   Application.EnableEvents = True
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[01Patrik]

Very nice solution... I can use that elsewhere. Have a shiny one

// Patrik