ms excel spreadsheet - fill in required cells before saving it

[Matt K.]

After creating an Excel (Excel 2003) spreedsheet, how can i get the user to
fill in the necessary/required cells (and telling the user to fill it in is
not good enough)? Or is there a way to have Excel check to make sure these
cells are filled in upon saving it?

 

[Don Guillett]

You can create a before close and/or before save macro that checks the cells
and refuses if not filled.

 

[Gord Dibben]

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
Dim myrange As Range
Set myrange = Sheets("Sheet1").Range("A1,A4,B3,C4")
If Application.WorksheetFunction.CountA(myrange) < 4 Then
MsgBox "Fill in all the mandatory cells"
Cancel = True
End If
End Sub

Copy/paste into Thisworkbook module.


Gord Dibben MS Excel MVP