Create a validation in a cell based on the value in another cell-CodePudding

I have 2 cells C7 and C8, I need to create a data validation that if C7 is 0 then only 0-12 should be allowed in C8 and it shows an error message if the value entered in C8 is greater than 12, and if C7 is greater than 0, then any number will be allowed in C8.

CodePudding user response：

Insert this code in the worksheet module

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

Insert this in a standard module

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

CodePudding user response：

I liked Elio Fernandes solution, the only thing about it was that after you had altered C8 you could change C7 afterwards and buypassing the Validation check. (I tried suggesting an edit on his post first, but I was afraid to mess up something ) This is just a copy of his solution with modifications to include Validation on C7 also.

Note the difference in _SelectionChange to set the validation on cell selection.

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