Introduction

In this Excel data validation example, you'll create an Excel Data
Validation drop down list that allows one specific user to
add new items. The new data validation items will be automatically
added to the drop down list, and the list will be sorted alphabetically.

This technique isn't foolproof, and anyone who's determined to circumvent
it would be able to. But, it's a good way to remind people that they
can't update the list without permission.

Set Up the Data Entry Sheet

For instructions on setting up the worksheets and List sheet sort
code, see the Excel Data
Validation - Add New Items page. Follow those instructions, up
to the Data Entry Code section. Then return to this page for the
Data Entry Code.

The Data Entry code shown here will allow only one specific user
to add new items. All other users will see a message that says they're
not allowed to add items.

Add the Data Entry Code

On the worksheet code module for the DataEntry sheet, you'll add
code that runs automatically if a change is made on the worksheet.

At the top left of the code window, click the arrow in the Object
drop down, and click on Worksheet. A couple of lines of code will
be automatically added to the code module, and you can ignore that
code, or delete it.

At the top right of the code window, click the arrow in the Procedure
drop down, and click on Change.

When the cursor is flashing, type or paste the following code, between
the Private Sub Worksheet_Change and End Sub lines.

Then, change the strAuth to the person you want to allow
to add new items.

Test the Data Entry Code

To see how the sort code works, add your name as the authorized user,
and you can add an item to one of the lists:

In the the Client column, type Ann, then press the Enter
key.

Click the drop down arrow in the Client column, and you'll see
that Ann now appears in the drop down list.

Check the Lists sheet, and you'll see that Ann was added to the
ClientList range, between Al and Bea.

If your name is different than that of the authorized user, you won't
be able to add an item to one of the lists:

In the the Client column, type Joe, then press the Enter
key.

A warning message appears, and the invalid entry is removed from
the data validation cell.

How the Data Entry Code Works

First, the code finds the user name associated with the copy of Microsoft
Excel:

strUser = Application.UserName

The EnableEvents property is set to False, so any other changes to
the worksheet will be ignored, while this code is running.

Application.EnableEvents = False

Then, the code checks to see if more than one cell was changed. If
so, the macro stops running:

If Target.Count > 1 Then GoTo exitHandler

Next, the code checks to which row was changed. If it was row 1,
where the headings are located, the macro stops running.:

If Target.Row > 1 Then
...
End If

Then, the code tries to set a range based on the data validation
cells in the worksheet. If there are no data validation cells, the
range can't be set, so the macro stops running:

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then GoTo exitHandler

Next, the code checks to see if the cell that was changed (Target)
intersects with the range of cells that contain data validation. If
it doesn't, the macro stops running:

If Intersect(Target, rngDV) Is Nothing Then GoTo exitHandler

Then, the code creates a text string, based on the data validation
formula in the changed cell. The Right function removes the first
character from the string. For example, if the data validation formula
is =ClientList the str variable would be set to ClientList:

str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)

Then, the code tries to set a range based on the str variable. If
there is no range with that name on the Lists sheet, the range can't
be set, so the macro stops running:

On Error Resume Next
Set rng = ws.Range(str)
On Error GoTo 0
If rng Is Nothing Then GoTo exitHandler

Then, the code checks that range, by using the COUNTIF worksheet
function, to see if the changed cell's value is already in that list.
If so, the macro stops running: