Data Validation? Restrict input if cell above is empty

[JumboCactuar]

Hi,
With range (C2:C10) I want to restrict input
For example:

If C2 is populated
Can input in C3
Can't input in C4:C10

Any help appreciated

 

[mumps]

What happens if C2:C5 is empty and C6 is populated? Do you want to all input into C7?

 

[gallen]

You leave a lot of information out. Here is the 'bare bones' and assumes you know a little about VBA.

This code needs to go into the worksheet code window. I've commented as much as possible so you can see what each line is trying to achieve.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sValidate As String 'Address of cells to validate
    
    'define the range address
    sValidate = "C2:C10"
    
    'Check user has changed a cell within our range
    If Not Intersect(Range(sValidate), Target) Is Nothing Then
        'run the validation function. Will return true if everything is ok
        If Validated(Target) = False Then
            MsgBox "Not allowed", vbCritical, "Error"
            With Application
                'disable events
                .EnableEvents = False
                'undo the user's action
                .Undo
                're-enable events
                .EnableEvents = True
            End With
        End If
    End If
End Sub




Private Function Validated(Target As Range) As Boolean
    
    'has user has changed a value in C4:C10?
    If Not Intersect(Range("C4:C10"), Target) Is Nothing Then
        'Is C2 Populated? If so exit function leaving Validated as 'False'
        If Range("C2") <> "" Then Exit Function
    End If
    
    Select Case Target.Address
        Case "C3" 'user has changed C3. Is it allowed?
            'not allowed to change if C2 is blank?
              If Range("C2") = "" Then Exit Function
        Case Else
            'subsequent cases can go here depending on what you are trying to achieve
    End Select
    'if we get here then it is validate
    Validated = True
End Function

 

[JumboCactuar]

Sorry for the confusion

With the range (C2:C10) it should restrict input if any of the above rows are blank.

Column B contain formulas which fill to row 10.
B2: =IF(C1="","",B1+1)

So you can see,if a value is entered in C3 but C2 is blank it returns a VALUE error

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a values in C2:C10 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2:C10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim rng As Range
    Select Case Target.Row
        Case Is = 3
            If Target.Offset(-1, 0) = "" Then
                MsgBox ("Please enter a value in cell C2.")
                Target.ClearContents
                Range("C2").Select
            End If
        Case Is = 4, 5, 6, 7, 8, 9, 10
            For Each rng In Range("C2:C" & Target.Row - 1)
                If rng = "" Then
                    MsgBox ("Please enter a value in cell " & rng.Address(0, 0) & ".")
                    Target.ClearContents
                    rng.Select
                    Exit For
                End If
            Next rng
    End Select
    Application.EnableEvents = True
End Sub

 

[JumboCactuar]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a values in C2:C10 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2:C10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim rng As Range
    Select Case Target.Row
        Case Is = 3
            If Target.Offset(-1, 0) = "" Then
                MsgBox ("Please enter a value in cell C2.")
                Target.ClearContents
                Range("C2").Select
            End If
        Case Is = 4, 5, 6, 7, 8, 9, 10
            For Each rng In Range("C2:C" & Target.Row - 1)
                If rng = "" Then
                    MsgBox ("Please enter a value in cell " & rng.Address(0, 0) & ".")
                    Target.ClearContents
                    rng.Select
                    Exit For
                End If
            Next rng
    End Select
    Application.EnableEvents = True
End Sub

Perfect thank you
This is just how I wanted it.


One small issue

If I manually clear cells C3:C10 and leave C2 populated
It debug errors with Type Mismatch at the line after (Case = 3)

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2:C10")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Dim rng As Range
    Select Case Target.Row
        Case Is = 3
            If Target.Offset(-1, 0) = "" Then
                MsgBox ("Please enter a value in cell C2.")
                Target.ClearContents
                Range("C2").Select
            End If
        Case Is = 4, 5, 6, 7, 8, 9, 10
            For Each rng In Range("C2:C" & Target.Row - 1)
                If rng = "" Then
                    MsgBox ("Please enter a value in cell " & rng.Address(0, 0) & ".")
                    Target.ClearContents
                    rng.Select
                    Exit For
                End If
            Next rng
    End Select
    Application.EnableEvents = True
End Sub