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.

EventHandlers

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:

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:

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

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:

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:

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.

You can turn on/off the List Editor using the Options, 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:

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

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:

Switch to the handlers sheet, select a cell in row 5, and click 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:

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.