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
        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.


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.

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy