How do I prevent saving an excel file if cells are blank?

R

Ron de Bruin

Hi Leighann

You can use the before save event

Copy this event in the Thisworkbook module
Change the sheet name and ranges in this line
Set myrange = Worksheets("Sheet1").Range("A1:A6,C10,D12,G1:G3")

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:A6,C10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "fill in all cells"
Cancel = True
End If
End Sub
 
Top