select multiple items from data validation list

Question

Hello,

This is related to a previous question "How to use a range for target address in VBA?" that was answered by Mike H....

Thank you Mike H...  !!

With Mike's fix to previously acquired code, we were able to use Data Validation that allows multiple items to be picked.

A second question quickly came up:

Now that we have this working for one column, can we make this happen for more columns on the same sheet?

For example, in column B, the Data Validation allows us to pick one or more items from a list of exams.

The question now is, in column C, can we set up Data Validation to allow us to pick from one or more items from a list of physical screenings. (see screenshot for example)

This is the code that we used for making 1 column have Data Validation with multiple selections possible. Is it feasible without getting too complicated to have more columns allow Data Validation with multiple selections?

Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com

' To Select Multiple Items from a Drop Down List in Excel Dim Oldvalue As String

Dim Newvalue As String

Dim Oldvalue As String

If Not Intersect(Target, Columns(3)) Is Nothing Then   ‘Mike H’s code which made things happen for the entire column

    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

    Else: If Target.Value = "" Then GoTo Exitsub Else

        Application.EnableEvents = False

        Newvalue = Target.Value

        Application.Undo

        Oldvalue = Target.Value

        If Oldvalue = "" Then

            Target.Value = Newvalue

        Else

            Target.Value = Oldvalue & ", " & Newvalue

        End If

    End If

End If

Application.EnableEvents = True

Exitsub:

Application.EnableEvents = True

End Sub

Thank you for any feedback.

Answer 1

Try the below code example. In the Case statement simply edit with the columns to which you require the data validation multi select to be applied

Reason for using Select Case in lieu of If statements is that the Case can have one or multiple conditions with the column numbers as per the example.

Note that lngCol is used in the following line of code

If Not Intersect(Target, Columns(lngCol)) Is Nothing Then

Modified/Additional code in bold.

Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com

    ' To Select Multiple Items from a Drop Down List in Excel Dim Oldvalue As String

    Dim Newvalue As String

    Dim Oldvalue As String

    Dim lngCol As Long

    lngCol = Target.Column

    Select Case lngCol

Case 3, 5, 7, 8, 9    'Edit with the required column numbers separated with commas

            If Not Intersect(Target, Columns(lngCol)) Is Nothing Then   'Mike H’s code which made things happen for the entire column

                If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

                GoTo Exitsub

                Else: If Target.Value = "" Then GoTo Exitsub Else

                    Application.EnableEvents = False

                    Newvalue = Target.Value

                    Application.Undo

                    Oldvalue = Target.Value

                    If Oldvalue = "" Then

                        Target.Value = Newvalue

                    Else

                        Target.Value = Oldvalue & ", " & Newvalue

                    End If

                End If

            End If

    End Select

    Application.EnableEvents = True

Exitsub:

    Application.EnableEvents = True

End Sub

Answer 2

Quick question. How do you set up multiple selection in Data Validation and where/how do all the selected values get displayed?

Answer 3

You could also add a line of code to exclude multiple cells in the change event like the following

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim Newvalue As String

    Dim Oldvalue As String

    Dim lngCol As Long

  If Target.Cells.Count <> 1 Then Exit Sub      'Line here to exclude simultaneous changes to multiple cells

    lngCol = Target.Column

    Select Case lngCol

Answer 4

You did not read my reply very well which contained the following statement.

Note that lngCol is used in the following line of code

If Not Intersect(Target, Columns(lngCol)) Is Nothing Then

Answer 5

Hello OssieMac,

Thanks for helping!

I tried the code and got a bug... I may be doing this wrong.

Here is the line with the bug

I have (3, 4) entered in 2 areas (the yellow line, and the one above)  - is that how I should be designating the columns?