Way back in April, I wrote about the Excel VBA code to automatically add new items to a Data Validation drop down. It’s an easy way to update a list as you work, so the latest items are always available for users.
Last week, someone wrote and asked how to modify that code, so only a specific user could add new items. Everyone else should see a message that says they aren’t permitted to add items.
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.
Identify the User
One way to find out who’s trying to add a new item, is to check the user name that’s entered in the Microsoft Office application. After you install Office, you can personalize it in Excel Options, in the Popular category, by entering your name in the User Name box.
In Excel VBA, you can create variables to capture that user name, and the name of the authorized user:
Dim strAuth As String Dim strUser As String strAuth = "Debra Dalgleish" strUser = Application.UserName
Block Non-Authorized Users
After you figure out who the user is, you can block them from doing something. In this workbook, we want to block all but one user from adding new items.
If strUser <> strAuth Then MsgBox "You do not have authority to add Work Order numbers. " _ & vbCrLf _ & vbCrLf _ & "Please check with Administrator before continuing." GoTo exitHandler
Remove the Added Item
The above code will block people from adding the new item to the data validation drop down, but doesn’t prevent them from typing the new item in the data validation cell. With another line of code, you can undo the invalid entry that they made.
Application.Undo GoTo exitHandler
Because the code might make a change on the worksheet, you’ll have to turn off the EnableEvents property. That will prevent the Worksheet_Change code from running again, while it’s in the middle of running the first time.
At the top of the procedure, add the line to turn off the EnableEvents property.
Application.EnableEvents = False
In the exitHandler, remember to turn EnableEvents back on.
Application.EnableEvents = True
Full Code and Sample File
If you’re interested in seeing the full code, or downloading the sample file, please visit the Contextures website, and read Excel Data Validation – Add New Items – Specific User.