Software

How to update and alphabetize a validation control list programmatically in Excel

Excel's basic validation is usually adequate, but sometimes you may need an on-the-fly solution. Here are the steps for using VBA to make it happen.

hero
Image: iStockphoto.com/DragonImages
Valid data is the cornerstone of any application. Thanks to Excel's Data Validation feature, input values can be tested and accepted or rejected based on criteria you specify. One of the easiest ways to validate data is to restrict users to a list. 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.

I'm using Excel 2016 desktop on a Windows 10 64-bit system, but most everything in this article applies to earlier Ribbon versions. The earlier .xls format (menu) doesn't support the Table object. For your convenience, you can download the example demonstration .xlsm and .cls files.

Something to consider

Before we start, let's take a minute to consider what this technique does. In a nutshell, it lets a user choose a value from the control's dropdown list or enter a value that's not yet in the list. After entering the new value, VBA code will update the list by adding the input value.

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. 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. Anytime you have to work this hard to accomplish what you need, I recommend taking a step back to reevaluate what you're doing and why. Perhaps Excel isn't the best software for your task; perhaps you need a database. Or Excel might be the right tool, but maybe there's an easier way to accomplish what you need. In the end, you might decide that this route is the best route—but do give it some serious thought. There's nothing inherently wrong with this technique, but there might be better options.

SEE: Microsoft Office 365: The smart person's guide

Setup

Now, let's continue by enhancing the simple chocolate list shown in Figure A to allow new input values. The example data validation control in E3 uses a Table object as the list source. If you need help creating this basic data validation control and its source list, please read How to use Excel's Data Validation feature to prevent data entry mistakes before you continue. In this article we'll:

  • Save the workbook as a macro-enabled workbook.
  • Assign a range name to the Table object so the data validation control can reference it as its source.
  • Disable the data validation control's error message.
  • Add the VBA procedure that accepts (or rejects) the new value, adds it to the source list, and then alphabetically sorts the list.

Figure A

Figure A

Let's enhance this simple validation list.

Because we're adding a VBA procedure, your first step is to save the workbook as a macro-enabled file. Choose Save As from the File tab, choose a location, and select Excel Macro-Enabled Workbook (*.xlsm) from the Save As Type dropdown, as shown in Figure B.

Figure B

Figure B

Save your workbook as a macro-enabled file.

The example data validation control (Figure A) uses a Table object as its source. For better or worse, you can't reference a Table object using data validation. You must create a range name for the Table and reference the range name.

Specifically, you'll reference the source Table by name (Chocolates) when creating a named range (Figure C) and then reference the range name when creating the validation control (Figure D).

Figure C

Figure C

Reference the Table when creating a range name.

Figure D

Figure D

Reference the range name you created for the Table.

If you don't know how to do this, refer to the "Using a Table source" section in How to use Excel's Data Validation feature to prevent data entry mistakes.

All those names can get a bit confusing. If it helps, consider tagging each with its purpose when applying this technique to your own work. To clarify this example, the names follow:

  • Chocolates: name of the Table object
  • Chocolates List: column name in the Chocolates table
  • ChocolatesList: range name that refers to the Chocolates table
  • Table: sheet name

Next, let's disable the control's error message. By default, a validation control will display an error when you enter a new value instead of choosing a list item, as you can see in Figure E.

Figure E

Figure E

Excel warns you when you enter an invalid value.

We want to inhibit this message so users can update the source list with new values. You can accomplish this as follows:

  1. Select the validation control (E3).
  2. Click the Data tab.
  3. From the Data Validation dropdown (in the Data Tools group), choose Data Validation.
  4. In the resulting dialog, click the Error Alert tab.
  5. Uncheck the Show Error Alert After Invalid Data Is Entered option (Figure F) and click OK.

Figure F

Figure F

Inhibit the error message.

You've disabled the error message and Excel will let you enter a value instead of choosing one from the list—but it won't do anything with the input value. To use that value, you'll need VBA.

Add the VBA code

We'll use a VBA procedure to capture an input value in the validation control, add it to the source list (the Chocolates Table), and then sort the new list in ascending order. To add the code, right-click the sheet tab and choose View Code to launch the Visual Basic Editor (VBE) or use your favorite method for launching the VBE. Next, add the code shown in Listing A. Don't try to cut and paste the code from this web page because the VBE will object to some of the web characters. Instead, use the downloadable .xlsm or .cls file.

Listing A

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

Dim rng As Range

Dim dbl As Double

'A few checks for invalid values before accepting.

'Exit procedure if selected cell isn't validation cell or cell is empty.

If Target.Address <> "$E$3" Then Exit Sub

If IsEmpty(Target) Then Exit Sub

'If value already exists in list, reject input value.

Set rng = ThisWorkbook.Worksheets("Table").Range("Chocolates[Chocolates list]")

If Not IsError(Application.Match(Target, rng, 0)) Then

dbl = Application.Match(Target, rng, 0)

Else

dbl = 0

End If

'If match is found, accept value and quit.

If dbl <> 0 Then Exit Sub

'If match isn't found, confirm new input value in validation text control.

lReply = MsgBox("Add " & Target & " to list?", vbYesNo + vbQuestion)

If lReply = vbYes Then

Range("ChocolatesList").Cells(Range("ChocolatesList"). _

Rows.Count + 1, 1) = Trim(Target)

Else

Exit Sub

End If

'Sort table.

With ActiveWorkbook.Worksheets("Table").ListObjects("Chocolates")

.Sort.SortFields.Clear

.Sort.SortFields.Add Range("ChocolatesList")

With .Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlAscending

.Apply

End With

End With

End Sub

To test the code, select the validation control and enter a new type of chocolate. First, the code checks the active cell's location passed in the Target variable. Why? Because Excel fires the sheet's Change event every time you change something in the sheet. By verifying that the active cell is the validation control cell and exiting the procedure immediately when it is not, you can work in the sheet doing other things without running the code that updates the list. The second IF checks for an empty cell. If there's no input value, there's no reason to complete the procedure; you won't want to add blanks to the list.

The next set of IFs compares the input value to the existing list. If the input value is already in the list, the code quits the procedure because you don't want to duplicate values in the source list. You might find the use of a Double variable odd, but don't change it in the name of efficiency: the MATCH() function requires a Double.

Moving on, the procedure displays the message shown in Figure G, asking the user to confirm the addition of the input value. Click No and Excel won't add the input value to the source list. If you click Yes, the code adds the input value to the bottom of the Chocolates Table and then sorts the Table. Figure H shows the resulting Table.

Figure G

Figure G

Confirm or reject the input value.

Figure H

Figure H

The procedure adds the input value to the source list.

When applying this procedure to your own work, be sure to update the following references:

  • Target.Address: $E$3 is the cell location of the validation control.
  • Range name: ChocolatesList is the range name given the Chocolates Table, to reference it in the validation control.
  • Sheet name: The example sheet name is Table because we're using a Table object as the list source.
  • Table name: The example Table name is Chocolates.

Simplified example

This example is simple on purpose. You might want the procedure to do more. You could add expressions to verify specific properties of the input value. For example, this code uses the TRIM() function to remove space characters. You should definitely add appropriate error handling.

How you're using the data validation control will determine how you use the selected values. There's no code for actually using a selected value—whether new or existing.

In addition, early in the procedure, the code checks the Target address. If it isn't E3, the code stops. Hard-coding an address is always a bit like cheating, and it usually comes back to bite something. In this case, the hard-coded address limits you to a single-cell data validation control. You might use this type of control for making a decision, but it wouldn't be conducive to easy data entry. For that purpose, you must save Target as a Public variable and then update Target in the first statement.

It's a needy solution. The good news is that it will have some valid applications—and even if you don't end up using it, you learned some pretty cool VBA for referencing Table objects.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox