Data Validation

  • Thread starter Thread starter cindy willard
  • Start date Start date
C

cindy willard

Subject: data validation
From: "cindy willard" <[email protected]> Sent:
7/16/2003 6:10:04 AM




I sure did not make my original question very
clear. What I want to do is make sure the users enter
required information into specific cells; such as:

department number(which is a 5-digit text field)
date of hire
rate of pay

I have created a template for the users and would like to
prevent them from saving the file if the above cells are
left blank.

-----Original Message-----

There are 16,777,216 cells per worksheet and the number of
worksheets is rather large.

Were there any cells in particular that you wished to fill
or do you really want a program that would fill all of
them?




.
Click to expand...
..
 
I sure did not make my original question very
clear. What I want to do is make sure the users enter
required information into specific cells; such as:

department number(which is a 5-digit text field)
date of hire
rate of pay

I have created a template for the users and would like to
prevent them from saving the file if the above cells are
left blank.
Click to expand...
...

Presumably the people who'd be using this can't create new departments, thus
department numbers, on their own. If department number isn't already entered
from a dropdown list, it should be. Enter something invalid, like '-----',
before you set up the data validation list. Use a defined name to refer to the
list of department numbers, like DeptCodeList.

Now you can use a Workbook_BeforeSave event handler to prevent users saving the
file if these cells are blank or invalid. IMO, it's easier to use defined names
or cells to store validation criteria expressions. In this case, if the
department, hire date and pay rate entries were cells named, respectively,
Department, HireDate and PayRate, then the validation expression could be the
defined name ValidEntries referring to

=AND(COUNTIF(DeptCodeList,Department),HireDate>BaseDate,PayRate>MinimumWageRate)

where BaseDate could be DATE(2003,1,1) and MinimumWage would be whatever the law
mandates. Then your Workbook_BeforeSave code could look like


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Evaluate("ValidEntries") Then Exit Sub

MsgBox Prompt:="Department, hire date and/or pay rate are invalid." & _
Chr(13) & "Please (re)enter.", Title:="File won't be saved!"

Cancel = True
End Sub
 
Back
Top