So while the example is simplified on purpose, keep this in mind when applying this technique to your own applications.In addition, this technique is rather complex for the results.
Allowing users to update a list negates one of the main reasons for using the list in the first place—to limit errors.
In this example, it would be next to impossible to ensure that the new value entered is spelled correctly, is relevant to the control's purpose, and so on.
You can even update that list in one of two ways: You can manually add new items to the source list or you can allow users to enter new values using the data validation control itself.
In this article, you'll learn how to update and alphabetize a data validation control's source list using VBA.
For your convenience, you can download the example demonstration and files.
Before we start, let's take a minute to consider what this technique does.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.The code calls the Has Validation function to ensure that the range named Validation Range still contains Data Validation.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. Thanks to Excel's Data Validation feature, input values can be tested and accepted or rejected based on criteria you specify.