Check cell filled before close

[Martin Los]

I have a 4 worksheets filled with data and mailed to me on
a daily basis. Sometimes a cell is being forgotten to be
filled with a value.

How can I get the person who fills the worksheet cells to
get a notice if he tries to shut the workbook without
having filled all the cells he has to fill?

TIA

Martin

P.S. I think of
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim wsh3 As Worksheet

Set wsh1 = Worksheets("Sheet 1")
Set wsh2 = Worksheets("Sheet 2")
Set wsh3 = Worksheets("Sheet 3")

'HERE I NEED A RANGE (CONTAINING ALL CELLS TO BE FILLED IN
THE 4 WORKSHEETS) ANY IDEA HOW?
If wshS1.Cells(6, 6) = "" Then MsgBox "You forgot a
cell!
......
or

varArray = IsEmpty(MiVar) Then MsgBox "You forgot a cell!"

End Sub

 

[Dave Peterson]

Maybe you can use something like this:

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

Dim iCtr As Long
Dim myRanges As Variant

myRanges = Array(Me.Worksheets("sheet1").Range("a1,b3,c9"), _
Me.Worksheets("sheet2").Range("c3:c8"), _
Me.Worksheets("sheet3").Range("d1:g1,z9"), _
Me.Worksheets("sheet4").Range("a2:a9"))

For iCtr = LBound(myRanges) To UBound(myRanges)
If Application.CountA(myRanges(iCtr)) = myRanges(iCtr).Cells.Count Then
'do nothing
Else
MsgBox "Noooooo! You must complete the form before you save!" _
& vbLf & _
"Look here first: " _
& myRanges(iCtr).Address(external:=True)
Cancel = True
Exit For
End If
Next iCtr

End Sub