如果第一个单元格已填写，则将行设为必填项

Question

您的条件链中有一个错误：

cond1 And cond2 Or cond3 Or cond4如果或True中的一个（或两个）为，则始终会求值为。这是因为会在之前求值。参见cond3cond4TrueAndOr文档了解更多信息。

您可以使用括号来调整评估顺序：

cond1 And (cond2 Or cond3 Or cond4)

此表达式True仅当cond1 和任何cond2, cond3, cond4都是True。

对于您来说，我建议对您的代码进行以下改进：

首先，阅读有关 VBA 中的循环。For ... Next这Do ... Loop 是您需要了解的两种类型。
然后，为了让事情更清楚一点（也就是说，让你的意图在代码中可见），把验证代码放入其自己的子程序/函数中。
最后，为了让发生的事情更加明显，你可以拆分这些长链条件。

如果我们现在看一下该Workbook_BeforeSave过程，它在那里做什么并不明显（对你来说可能很明显，因为你刚刚把代码放在那里，它在你的脑海中还很新鲜 - 但在 3/6/12 个月后回到这张表，你首先需要了解它在做什么）。让我们来解决这个问题：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Cancel = IsUserInputMissing

End Sub

Private Function IsUserInputMissing() as Boolean

    ' Validation code goes in here

End Function

现在，每个查看代码的人都可以一眼看出保存工作簿时会发生什么。

这行

If Sheets("Sheet1").Range("A7").Value <> "" And Sheets("Sheet1").Range("B7").Value = "" Or Range("C7").Value = "" Or Range("D7").Value = "" Or Range("E7").Value = "" Or Range("F7").Value = "" Or Range("G7").Value = "" Or Range("H7").Value = "" Or Range("I7").Value = "" Or Range("J7").Value = "" Or Range("K7").Value = "" Or Range("L7").Value = "" Or Range("M7").Value = "" Or Range("N7").Value = "" Or Range("O7").Value = "" Then

看起来不太友好。逐字逐句地写着If FirstCellIsNotEmpty And AnyFollowingCellIsEmpty Then。让我们这样编码。

Dim FirstCellIsEmpty as Boolean
FirstCellIsEmpty = Sheets("Sheet1").Range("A7").Value = ""

Dim AnyFollowingCellIsEmpty as Boolean
AnyFollowingCellIsEmpty = WorksheetFunction.CountBlank(Sheets("Sheet1").Range("B7:O7")) > 0

If Not FirstCellIsEmpty And AnyFollowingCellIsEmpty Then
    MsgBox "I don't know any Swedish. But please fill out the necessary cells."
End If

请注意使用WorksheetFunction.CountBlank以避免必须输入要检查的每个单元格。

最后，我将举例说明你的IsUserInputMissing功能可以看起来像。（不过，仍有很大的改进空间。）

Private Function IsUserInputMissing() As Boolean

    ' Easy way to set the beginning of the range
    Const FirstRowToBeChecked As Long = 7
    ' Set a reference to the sheet that needs checking
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("Sheet1")

    Dim iRow As Long
    iRow = FirstRowToBeChecked

    Do

        Dim FirstCellIsEmpty As Boolean
        FirstCellIsEmpty = Ws.Cells(iRow, 1).Value = vbNullString ' vbNullString is a clearer way of saying ""
        ' Exit loop at the first empty row
        If FirstCellIsEmpty Then Exit Do

        Dim AnyFollowingCellIsEmpty As Boolean
        AnyFollowingCellIsEmpty = WorksheetFunction.CountBlank(Ws.Range(Ws.Cells(iRow, 2), Ws.Cells(iRow, 15))) > 0

        If AnyFollowingCellIsEmpty Then
            ' Any time the requirements are not met, notify user and abort checking and saving
            ' This is not as elegant as checking the whole range and collecting info about all missing inputs
            ' But it's way easier to code :)
            MsgBox "I don't know any Swedish. But please fill out the necessary cells. Tack!"
            IsUserInputMissing = True
            Exit Function
        End If
        ' Don't forget to increment the counter, otherwise you've got yourself an endless loop
        iRow = iRow + 1

    Loop

    ' If execution reaches this line, all rows fulfil the requirement
    ' IsUserInputMissing will have its Default value: False
End Function

慢慢来，慢慢解决。我尽量把事情说清楚。但如果有什么不清楚的地方，请随时询问。:)

Answer 1