ms excel spreadsheet - fill in required cells before saving it

  • Thread starter Thread starter Matt K.
  • Start date Start date
M

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?
 
You can create a before close and/or before save macro that checks the cells
and refuses if not filled.
 
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
 
Thats brilliant, just tried it and it works like a dream, I have a question on the back of this, I have set up several forms that have a mixture of mandatory fields and some that if a user enters data into a field a following set of fields need to then become mandatory, is there a way of doing this within the same form?
 
Back
Top