除了Workbook_BeforeSave事件之外，还应创建Workbook_BeforeClose事件。检查单元格是否有错误，如果需要，显示消息，然后取消关闭事件，以防止用户在更正错误之前退出。取消关闭事件后，不会出现要求保存工作簿的弹出窗口。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim rng As Range
    Dim c As Range
    Dim a As Variant
    
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:C10")
    
    For Each c In rng
        If IsError(c.Value) Then
            a = MsgBox(Prompt:="Check cells for #VALUE! and correct them before saving.", Title:="Check Cells")
            Cancel = True
            Exit For
        End If
    Next
End Sub

如果您希望用户在工作簿有错误时仍然能够关闭，但不能保存。则显示YesNo消息框，并询问用户是否要关闭而不保存。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim rng As Range
    Dim c As Range
    Dim a As Variant
    
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:C10")
    
    For Each c In rng
        If IsError(c.Value) Then
            a = MsgBox(Prompt:="Check cells for #VALUE! and correct them before saving.", Title:="Check Cells")
            If MsgBox("Close without saving?", vbYesNo) = vbYes Then
                Application.EnableEvents = False
                ThisWorkbook.Close SaveChanges:=False
                Application.EnableEvents = True
            Else
                Cancel = True
            End If
            Exit For
        End If
    Next
End Sub