Chapter 8. Validation Lists

Chapter 8. Validation Lists

In this chapter, we will create validation lists for Account, Company, and Item columns.

Moreover, we will configure this feature in a database, and the add-in will apply validation rules automatically.


Configuring validation lists requires the EventHandlers table in a database.

We have created this table using the SQL code provided in the Configuring Database chapter.

Let's add a worksheet, named as handlers, run Data Connection Wizard and connect to the EventHandlers table:

Connect to the EventHandlers table

Uncheck Enable Query List on the ribbon. Click Finish, and insert the table at cell B3.


We have to see the following table:

The initial EventHandlers table in Excel

The add-in highlights fields that require values. Let's format the worksheet and table to make them beautiful.

Static Validation Lists

Add the following configuration and click the Save button:

Add ValidationList handlers

This table has two parts, "tables" and "handlers":

The first part defines a configured object

The last part defines a handler

In the table part, we have specified the configured table, its columns and the event name as ValidationList.

In the handler part, we have defined how to handle events. In this case, we have configured the VALUES type and specified static values in the HANDLER_CODE field.

Further, I will use the short format like this, without the SCHEMA fields that contain the dbo69 value:

Short format of the EventHandlers table used futher

Now, the handlers worksheet should look like this:

Configured EventHandlers table in Microsoft Excel


Let's switch to the payments worksheet and click Reload, Reload Data and Configuration:

Reload Data and Configuration


The add-in creates validation lists using the specified values:

Sample of Excel validation lists configured with the EventHandlers table

List Editor

The add-in activates List Editor that allows selecting values from lists in a comfortable way, including search.

Sample of the List Editor


You can turn on/off the List Editor using the Options, Show List Editor Task Pane option.

Show List Editor Task Pane option

Dynamic Validation Lists

Let's create a validation list for the Company column. We must have the possibility to change values easily.

Create an Excel table with companies at a new worksheet starting cell C3:

Create an Excel table with companies

Run Publish Wizard as described above. At the design tab, we have the following structure:

Design a database table of companies

The table contains one column that is used as a primary key column. It's ok. Click Next and execute next steps.

Publish table as dbo69.Companies table.

As a Publish Wizard result, we have the editable dbo69.Companies table of companies at a new sheet:

Publish a table of companies to a database

Let's remove the initial Sheet1 worksheet, rename the Sheet2 to companies, and format the table:

Remove the initial worksheet

Switch to the handlers sheet, select a cell in row 5, and click the Copy and Insert Rows button.

Use the Copy and Insert Rows button

This action will create a copy of the selected line. So, you can easily change only different values.


Add a validation list configuration for the Company field like this:

Add the ValidationList handlers with the table of companies

Complete table:

Complete table of event handlers

As a result, the add-in will create a validation list for the Company column using the Company field of the dbo69.Companies table.

Switch to the payments worksheet and click Reload, Reload Data and Configuration to refresh the list:

Reload Data and Configuration to refresh the list of companies



You have to know that the add-in places connected tables of validation list source values like dbo69.Companies to a hidden worksheet and configures validation lists using a range formulas.


Setting validation lists this way has great benefits.

You configure it in the EventHandlers table, and the add-in creates validation lists automatically.

Just use Reload, Reload Data and Configuration to reload validation list values.