Dynamic Data Validation Lists in Userforms

Let's say we need to assign staff to work different shifts and no person can work both early and late shifts.

Using data validation lists we can assign someone to the shifts, but having assigned that person to the first shift, it would be useful to remove their name from the list so they can't also be assigned to the second shift.

There are a couple of ways we can achieve this in a userform, the first is with dynamic arrays, and the second is with a good old VBA loop. Let's look at both approaches.

Note: Dynamic Array formulas are currently only available to Office 365 users on the Insider Channels.

Userform Design

I'm going to create two simple userforms, the first will use dynamic arrays, the second will not. So if you don't have Office 365 you can use the 2nd form. Both forms have two Combo Boxes to allow us to choose the staff for both shifts.

I've added a button so the forms can be closed and a couple of Text Boxes beside the Combo Boxes to indicate which work shift we are choosing someone for.

Using Dynamic Arrays to Populate the Combo Boxes

On my worksheet I've set up a table called Names, and I've added my staff members. You can have as many staff as you want.

In C2 I have this formula to filter out the name from E2.

The name in E2 is changed by the userform code, we'll come to that in a bit.

Using the Dynamic Array UserForm

When this form is loaded, the list of names for the Early Shift (the list in the first combo box) is read in from the Names table.

When a name is chosen for the Early Shift, the list for the Late Shift can be loaded minus that name. Firstly, the name of the person on the Early Shift is written to E2. This causes the FILTER function in C2 to generate the list of names for the 2nd shift, which are read into the second combo box.

Using the 'Standard' UserForm

As with the dynamic array form, when this userform is loaded, it reads the names from the Names table into the first combo box.

When a name is chosen for the first shift, the code has to loop through all the names adding only those names to the 2nd list that have not been chosen for the first shift.

Ensuring the Choices in Both Lists Are Different

Having chosen someone for both shifts, if you decide to go back to the first list and choose the person assigned to the second shift, their name is removed from the second list. Both forms work this way.