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

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
Back
Top