Ensuring That Data Validation Is Not Deleted

Category: General VBA | [Item URL]

Excel's Data Validation feature is very useful, but it has a serious flaw: It is easy for a user to accidentally (or intentionally) delete the validation rules. For example, you copy a range of cells and then paste them to a range that contains Data Validation, the Data Validation will be destroyed.

This tip describes a technique to help prevent this.

Naming the range

Set up your Data Validation as usual. Then, select all of the cells that use Data Validation, and name this range ValidationRange. This can be a contiguous range or a non-contiguous range.

Important: Ensure that every cell in the ValidationRange actually contains Data Validation.

The VBA code

The code below use an event procedure, and it must be located in the code module for the worksheet. For example, if your worksheet is named Sheet1, the code will go in the module named Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
'   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

How it works

The Worksheet_Change procedure is executed whenever a cell or range is changed on the worksheet. The code calls the HasValidation function to ensure that the range named ValidationRange still contains Data Validation. If every cell in that range contains Data Validation, the function returns True, the procedure ends, and no action is taken.

If one or more cells in the ValidationRange range no longer contain Data Validation, the function returns False. This means that the user has wiped out the Data Validation in one or more cells. In such a case, the last operation is undone, and the user sees the message below.

Limitations

This procedure handles the following operations, which would normally delete the Data Validation rules:

• Cutting/pasting data
• Copying/pasting data
• The Edit - Clear - All command
• The Edit - Delete command

However, it will not detect deleting entire rows or columns that contain data validation.