Creating mandatory fields(cells)...

S

SeattleKurt

I'm an estimator who uses excel to enter data for jobs. There are som
fields that will ALWAYS need to have something in them. Is there a wa
that I can format these cells so that if I try and save before they ar
filled, I get an error?

Thanks so much
 
D

Dave Peterson

Saved from a previous post:

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

Dim myRng As Range
Dim myEmptyRng As Range
Dim myCell As Range

Set myRng = Me.Worksheets("Form").Range("a1,b9,c12,d13")

If Application.CountA(myRng) > 0 _
And Application.CountA(myRng) < myRng.Cells.Count Then
Cancel = True
For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
If myEmptyRng Is Nothing Then
Set myEmptyRng = myCell
Else
Set myEmptyRng = Union(myEmptyRng, myCell)
End If
End If
Next myCell
MsgBox myEmptyRng.Address(0, 0) & " must have valid data!"
End If
End Sub

If all the cells are empty, you can still save (nice for changing the master).
But there are other techniques if you don't want to allow all empty cells.

this code goes behind the ThisWorkbook module.

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