如果 Excel 中第一列有值，则将第二列设为必填项

Question

我有两列。第一列是包含“是”和“否”的下拉列表。如果第一列中只有值，则第二列将成为必填项。请指导我该怎么做？

我想检查这个BeforeSave事件的验证。

我的代码是

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) 
    Dim rngCell As Range
    Dim rngCell2 As Range

    If ActiveSheet.Name="ABC" Then
        Dim lngLstRow As Long 
        lngLstRow = ActiveSheet.UsedRange.Rows.Count
        
        For Each rngCell In Range("A1:A" & lngLstRow)
            If rngCell.Value <> 0 Then 
                For Each rngCell2 In Range("B1:B" & lngLstRow)
                    If rngCell2.Value = 0 Then 
                        MsgBox ("Please enter a name in cell " & rngCell2.Address) 
                        rngCell2.Select
                    End If
                Next
            End If
        Next 
    End If
End Sub

它不起作用。陷入无限循环。

Answer 1

您可以直接遍历 A 列中使用的范围内的每一行，然后使用偏移量查看 B 是否已填充。

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

Dim rngCell As Range
Dim lngLstRow As Long

If ActiveSheet.Name = "ABC" Then

    lngLstRow = ActiveSheet.UsedRange.Rows.Count

    For Each rngCell In Range("A1:A" & lngLstRow)

        If rngCell.Value <> "" And rngCell.Offset(0, 1) = "" Then
            
            MsgBox ("Please enter a name in cell " & rngCell.Offset(0, 1).Address)
            
            rngCell.Offset(0, 1).Select
            
            Cancel = True
            
            Exit For
            
        End If
    
    Next

End If

End Sub