Validating and copying link data

by  |  22-Mar-2016 01:36

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.

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

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.

If Has Validation(Range("Validation Range")) Then Exit Sub Else Application.

Undo Msg Box "Your last operation was canceled." & _ "It would have deleted data validation rules.", vb Critical End If End Sub Private Function Has Validation(r) As Boolean The Worksheet_Change procedure is executed whenever a cell or range is changed on the worksheet.

Community Discussion