在我的 Excel 表中,我正在验证特定列不应为空且其值应唯一。此验证仅应在“Sheet1”上执行,但它也适用于其他工作表。我的代码是
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean)
Dim rngCell As Range
Dim lngLstRow As Long
lngLstRow = ActiveSheet.UsedRange.Rows.Count
For Each rngCell In Range("A1:A" & lngLstRow)
If rngCell.Value = 0 Then MsgBox ("Please enter a name in cell " & rngCell.Address) rngCell.Select
End If
Next
End Sub
对唯一值的第二次验证是通过 Excel 中的数据验证功能完成的。
答案1
您应该指定想要代码作用于的工作表,而不是使用 ActiveSheet。
因此,如果您想检查 Sheet1,您可以将其更改为:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rngCell As Range
Dim lngLstRow As Long
lngLstRow = Sheet1.UsedRange.Rows.Count
For Each rngCell In Sheet1.Range("A1:A" & lngLstRow)
If rngCell.Value = 0 Then
MsgBox "Please enter a name in cell " & rngCell.Address
Sheet1.Activate
rngCell.Select
End If
Next
End Sub