Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?
 
In the Thisworkbook module: ** Sample Data Only** Change Rng addresses to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set Rng = Range("B8,B11,E11,B14,E14")
For Each c In Rng
If c = "" Then MsgBox "Please Fill In Cell " & c.Address & " before
Closing."
If c = "" Then Cancel = True
Next c

End Su
 
Looking back... I might should restrict this code to the Specific Sheet that
your
Form is on - so that other sheets are not affected...

So line 2 should probably be:
Set Rng = Worksheets("MyFormSheet").Range("B8,B11,E11,B14,E14")

Just now starting to understand this stuff..

Jim
 
OK, this is it << I think>>

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveSheet.Name = Sheets("Sheet1").Name Then
Set Rng = Sheets("Sheet1").Range("B8,B11,E11,B14,E14")
For Each c In Rng
If c = "" Then MsgBox "Please Fill In Cell " & c.Address & " before
Closing."
If c = "" Then Cancel = True
Next c
End If
End Sub
 
Thanks for the help. I looked at the link--some great stuff--but I cannot
find information on validating to ensure that a cell(s) is not empty.

Could you point me to the info.

Thanks.

JoeP
 
Joe

If the users ignore the cell you want filled, there is no way to force them to
fill it. DV works only when an attempt to enter data is performed.

In a cell adjacent to the fill-in cells you could have a formula like

=IF(A1="","Please fill-in A1 before moving on","A1 is OK") as a reminder

You could place conditional formatting on the cell to have it colored until
users enter data.

Just as a reminder, of course, not as a way to enforce entering data.

Alternative................

You could use workbook beforeclose code to stop the closing and saving of the
workbook if certain cells are not filled, along with a message reminding them to
go back and do it right.

Then they will screw you over by disabling macros when opening the workbook.

Then you will have to deal with that event.

Easy huh?


Gord Dibben MS Excel MVP
 
Is there a way to validate more than one criteria on a cell? For example I
want to validate that a cell is all caps and has 25 or less characters.

Thanks
 
How can I keep a validation when someone pastes data in from another
spreadsheet? Currently when the user pastes in data the validation is lost.
 
Back
Top