Ensuring That Data Validation Is Not Deleted
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.
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
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