Something like this
- test in attached workbook
In sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 1 Then Exit Sub
If .Offset(, -1).Value = "" Then
Application.EnableEvents = False
.ClearContents
.Offset(, -1).Select
Application.EnableEvents = True
End If
End With
End Sub
In ThisWorkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If AllRowsComplete = False Then
Cancel = True
MsgBox "Save cancelled until you finish the job properly!"
End If
End Sub
Function AllRowsComplete() As Boolean
Dim r As Long, rng As Range
With Sheets("Sheet1")
r = .Range("A" & Rows.Count).End(xlUp).Row
Set Rng = .Cells(1).Resize(r, 12)
End With
With Application.WorksheetFunction
If .CountA(Rng) = 12 * r Then
AllRowsComplete = True
Else
AllRowsComplete = False
End If
End With
End Function
Bookmarks