根據另一個單元格中的值在一個單元格中創建驗證

我有 2 個單元格 C7 和 C8，我需要創建一個資料驗證，如果 C7 為 0，那么在 C8 中只允許 0-12 并且如果在 C8 中輸入的值大于 12，并且如果 C7 顯示錯誤訊息大于 0，則 C8 中將允許任何數字。

uj5u.com熱心網友回復：

將此代碼插入到作業表模塊中

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C7")) Is Nothing Then
        If Range("C7").Value = 0 Then
            Call DataValidation_Create
        Else
            Call DataValidation_Delete
        End If
    End If
End Sub

將其插入到標準模塊中

Sub DataValidation_Create()
    With Range("C8").Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="0", Formula2:="12"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Choose a value"
        .ErrorTitle = "Choose a value between 0 - 12"
        .InputMessage = "Insert a value between 0  - 12"
        .ErrorMessage = "Insert a value between 0  - 12"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Sub DataValidation_Delete()
    With Range("C8").Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

uj5u.com熱心網友回復：

我喜歡 Elio Fernandes 解決方案，唯一的問題是在您更改了 C8 之后，您可以在之后更改 C7 并通過驗證檢查。（我先嘗試建議對他的帖子進行編輯，但我害怕搞砸了）這只是他的解決方案的副本，并進行了修改以包括 C7 上的驗證。

請注意_SelectionChange設定單元格選擇驗證的不同之處。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C7:C8")) Is Nothing Then
        If Range("C7").Value = 0 Then
            Call DataValidation_Create(Range("C8"))
        Else
            Call DataValidation_Delete(Range("C8"))
        End If

        If Range("C8").Value > 12 Then
            Call DataValidation_Create(Range("C7"))
        Else
            Call DataValidation_Delete(Range("C7"))
        End If
    End If

End Sub


Sub DataValidation_Create(pCell As Range)
    If pCell.Address = "$C$8" Then
        With Range("C8").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, 
                 _Operator:=xlBetween, Formula1:="0", Formula2:="12"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = "Choose a value"
            .ErrorTitle = "Choose a value between 0 - 12"
            .InputMessage = "Insert a value between 0  - 12"
            .ErrorMessage = "Insert a value between 0  - 12"
            .ShowInput = True
            .ShowError = True
        End With
    ElseIf pCell.Address = "$C$7" Then
        With Range("C7").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, 
                 _Operator:=xlGreater, Formula1:="0"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = "Choose a value"
            .ErrorTitle = "Choose a value greater than 0"
            .InputMessage = "Choose a value greater than 0"
            .ErrorMessage = "Choose a value greater than 0"
            .ShowInput = True
            .ShowError = True
        End With
    End If
End Sub

Sub DataValidation_Delete(pCell As Range)
    With pCell.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub 

標籤：擅长 vba 验证 excel-公式

上一篇：帶回圈的ExcelVBACountifs

下一篇：SwiftUI-從子視圖轉換回主視圖后，移除NavigationBar中的額外空間