Allow Only Specific User to Change Excel List

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.

User Name box in Excel Options
User Name box in Excel Options

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

Disable Events

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.

___________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.