Reliable Data Validation

Tomek has two cells, B3 and B4, both of which have data validation applied to them. In cell B3, data validation requires a whole number between 1 and 5. In cell B4, data validation requires a whole number between 6 and 10. If Tomek selects the range B3:B4, types 3, and presses Ctrl+Enter, then both cells end up with 3 in them. This, despite the data validation rule in cell B4. Excel only pays attention to the data validation rule, if any, in the first cell in the selected range. Tomek wonders if there is a way to enforce data validation on individual cells in a selection so this doesn't happen.

The short answer is that there are several loopholes (for lack of a better term) where data validation doesn't work. Tomek has identified one such loophole—the entry of values into a range of cells using Ctrl+Enter. Since that is the specific condition about which Tomek is asking, there is a relatively easy macro-based approach that can be employed. The following macro can be added to the worksheet's code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range

    Application.EnableEvents = False
    For Each c In Target
        If Not c.Validation Is Nothing Then
            On Error Resume Next
            If c.Validation.Type <> 0 Then
                If Not c.Validation.Value Then
                    MsgBox "Invalid entry in cell " & _
                      c.Address(False, False), vbExclamation
                    c.ClearContents
                End If
            End If
            On Error GoTo 0
        End If
    Next c
    Application.EnableEvents = True
End Sub

Every time a change is made in the worksheet, the macro runs, checking each cell that is selected. If the cell has a data validation rule set, then it is checked. If the rule fails, then the user is notified and the next cell is checked.

Notice that the macro will work with any changes done in the worksheet; it doesn't matter where they are done. It is possible to modify the macro to only check if the change was in the range B3:B4, but I suspect that the example range was just for example, not for limitation of where edits are expected.

There are three obvious drawbacks to using an approach such as this. The first has already been implied: The macro doesn't deal with all potential data validation loopholes, such as pasting a value to overwrite data validation rules. The second drawback is that using the user won't be able to use Ctrl+Z to undo edits. And, of course, the third drawback is that the workbook must be macro enabled.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12240) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.