Selecting Multiple Items in Data Validation List

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

If you want to limit the choices available for the values that can be entered into a cell, you could use a data validation list.

In the example image below, we are creating a roster for the teachers that are on playground duty during the week.

The data is laid out as a table, and I am using conditional formatting to highlight any duplicates across rows, and down columns. No teacher can be on duty twice in the same day, or be on duty for the same period during the week.

So when I select Claire for A.M. Recess & Lunch on Thursday, my conditional formatting rules warn me. Likewise when I assign Bob to A.M. Recess & Lunch on Friday, and Lunch on Wednesday, the CF rules let me know.

This works nicely but we have to set up data validation for each cell in the table. If we write a little VBA, we can use just one DV list to populate the entire row.

In the image below we can see I am using just one list at the start of each row, and each time I make a selection, the next empty cell in the row is populated.

Not only that, if I make a mistake, I can choose the blank first entry in the list, and this deletes the last value in the row. Or I can press the Delete key to do the same thing.

I can also delete cells in the middle of the row (and they don't have to be adjacent cells), then use the list to fill in the gaps.

This table is using the same conditional formatting rules as the first table.

VBA Code

The VBA uses the Worksheet_Change event so you'll find the code in the Sheet1 module.

Right click this link and download the .xlsm workbook

Option Explicit

' https://www.myonlinetraininghub.com/selecting-multiple-items-in-data-validation-list
' Written by Philip Treacy
'
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    
    If Target.Count > 1 Or (Target.ListObject.HeaderRowRange(Target.Column - Target.ListObject.HeaderRowRange.Column + 1) <> "Monday") Or (Not Intersect(Target, Target.ListObject.HeaderRowRange) Is Nothing) Then
    
        On Error GoTo 0
        Exit Sub
        
    End If
        
    Dim PreviousValue As String
    Dim NewValue As String
    Dim LastTableCol As Long
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Error GoTo 0
    
    LastTableCol = Target.ListObject.HeaderRowRange.Column + (Target.ListObject.HeaderRowRange.Columns.Count - Target.ListObject.HeaderRowRange.Column + 1)
    NewValue = Target.Value
    Application.Undo
    PreviousValue = Target.Value
    
    If PreviousValue = "" Then
    
        Target.Value = NewValue
        
    Else
    
        If NewValue = "" Then
        
            Target.Offset(, -1).End(xlToRight).Value = NewValue
            
        Else
            ' I would normally use .ListObject.Name to test if cell was in the table
            ' but this wasn't working correctly at this point. A Bug?
            ' so I'm checking that the column is inside the table
            
            On Error Resume Next
            
            If Target.Offset(, -1).End(xlToRight).Offset(0, 1).Column <= LastTableCol Then
            
                Target.Offset(, -1).End(xlToRight).Offset(0, 1).Value = NewValue
                
            End If
            
            On Error GoTo 0
            
        End If
    
    End If
        
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Sharing is Caring

Think this is the best thing since Slicers? Then please click the buttons below to share it with your friends and colleagues.