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

Hello OssieMac,

Thank you for your help with this question. It works perfectly.

I am wondering if I assigned your answer correctly. When I look in the forum, I still see this post as needing an answer, as opposed to the green check. Is it because I am looking at my own question? Let me know because I want to mark the code as the answer. Thank you for your help with this question.

Answer 3

Hello again,

It's Monday and I'm back at this. Sometimes it's good to take a break.

The run-time error happens when I set up Data Validation for a certain cell in column 3 (for example) and then use autofill.

If I click Debug, it shows this in the code

BUT, if select the entire column then set up Data Validation, there is no error.

So that's all it was.

The code works.

Thank you OssieMac !!

Answer 4

Hello OssieMac,

It was not clear in my email, but I had tried that too. I got an error message either way.

I may not be able to get back to this until Monday. But I hope to get this working, and will get back to you soon.

Thank you