Avoid empty cells in a column Range

[robertvdb]

I have a sheet where the user must enter a value in the range("A3:A12"). See enclosed image.

If he accidentally deletes one of these cells then VBA traps the error, see the code below.

But when he deletes MORE THAN ONE one cell then error 13 "type mismatch".

Any ideas ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col_A_rng As Range
Set col_A_rng = Range("A3:A12")

If Target.Count > 0 And Not Application.Intersect(Target, col_A_rng) Is Nothing Then
   
    If Target.Value = "" Then
        Target.Select
        Application.EnableEvents = False
        MsgBox ("you must enter a value"), vbCritical
        Application.Undo
        Application.EnableEvents = True
    End If

End If

End Sub

 

[Legacy 143009]

Yes, because you're going to have multiple ranges. Try like that:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col_A_rng As Range
Set col_A_rng = Range("A3:A12")

If Target.Count > 0 And Not Application.Intersect(Target, col_A_rng) Is Nothing Then
    If Target.Cells(1).Value = "" Then
        Target.Cells(1).Select
        Application.EnableEvents = False
        MsgBox ("you must enter a value"), vbCritical
        Application.Undo
        Application.EnableEvents = True
    End If
End If

End Sub

 

[robertvdb]

Thanks Flashbond ! Works fine !

 

[Legacy 143009]

Glad it did help! Thanks for the feedback