Data Validation Under Table

Tables are great, we love them. But we don’t always love them. If using data validation in a column, it works great inside of a Table. But what about in the cells underneath the Table? Not so much. In this blog post we have a VBA solution to let this work for you. This is a two-flavor solution, in that we use a Selection_Change event to duplicate the validation, and then we append the data to the Table. We’ll post the full solution here of course, for simplicity.

The below image shows an example of this working in action (click on the image to see the animated gif).

extendvalidation

How it works

When you select a cell directly under a Table, the code automatically looks to see if there is validation in the first available body cell above it (this doesn’t include the Total Row). If so, it copies the validation to the next cell directly under that column (based on the selection).

CAUTION: If there is already data validation in the target cell, nothing will happen.

Once we copy the validation with the Selection_Change event, the next part is to, when a selection is made, add that data into the Table (we’ll use the code in the above link from one of our previous blog posts to make this happen).

Copy the below code into the worksheet module of the desired sheet. This will make it work on all Tables in the worksheet. If you want this to work with all Tables in the entire workbook, there’s a slight adjustment, which we’ll address at the end of this post.

Getting it to work on a single Worksheet

Copy and paste the below code into the desired worksheet module (right-click worksheet tab, select View Code).

 

Getting it to work on all Worksheets

You’re going to have almost the exact same code. The event call lines themselves change slightly, and instead of referencing the Me object it will be the Sh object. The below code goes into the ThisWorkbook module.

The function HasValidation can really go anywhere. If you’re going to use it elsewhere, put it in a standard module.

Below is a link to a sample file. The code is working on all worksheets. The code for a single worksheet is in the worksheet module, but commented out.

extendvalidationbelowtable_example

Leave a Reply

Your email address will not be published. Required fields are marked *