How to create multiple multi option dropdown columns on one excel sheet?

Question

I am using the below VBA to allow a multi select in column L of sheet 1. The code also allows deselection within the cell. What I need is for multiple columns in the same sheet to allow multi select (using different data lists), columns E, L and P specifically. I have not been able to find the changes needed to the VBA to accomplish this. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oldVal As String 

Dim newVal As String 

Dim items As Variant 

Dim i As Integer 

Dim updatedVal As String 

Dim alreadyExists As Boolean 

' Disable events to prevent infinite loops 

Application.EnableEvents = False 

' Check if the changed cell is within L1:L101 

If Not Intersect(Target, Me.Range("L1:L20")) Is Nothing Then 

    ' Ensure the changed cell has data validation 

    If Target.Validation.Type = 3 Then  ' 3 refers to List validation 

        ' Capture new selection 

        newVal = Target.Value 

        Application.Undo ' Undo to get the old value 

        oldVal = Target.Value 

        ' If the cell was empty before, just set the new value 

        If oldVal = "" Then 

            Target.Value = newVal 

        Else 

            ' Convert old values to an array 

            items = Split(oldVal, ", ") 

            updatedVal = "" 

            alreadyExists = False 

            ' Check if the new selection is already in the list 

            For i = LBound(items) To UBound(items) 

                If Trim(items(i)) = Trim(newVal) Then 

                    alreadyExists = True ' Item already exists (needs to be removed) 

                Else 

                    ' Rebuild the list without removing other items 

                    If updatedVal = "" Then 

                        updatedVal = items(i) 

                    Else 

                        updatedVal = updatedVal & ", " & items(i) 

                    End If 

                End If 

            Next i 

            ' If the item was already in the list, remove it (deselect) 

            If alreadyExists Then 

                Target.Value = updatedVal 

            Else 

                ' Otherwise, add it to the list 

                If updatedVal = "" Then 

                    Target.Value = newVal 

                Else 

                    Target.Value = updatedVal & ", " & newVal 

                End If 

            End If 

        End If 

    End If 

End If 

ExitHandler:

' Re-enable events 

Application.EnableEvents = True 

Exit Sub 

End Sub

Answer 1

Hello TRambo_47,         Thanks for visiting Microsoft Community.Problems related to  VBA require more professional technical information to help you solve them. This question is more suitable for publishing on Stack Overflow  (English only).Post VBA programming questions to Stack Overflow by using the vba tag, along with any other relevant tags.Please note that Stack Overflow has guidelines such as requiring a descriptive title, a complete and concise problem statement, and sufficient details to reproduce your issue. Feature requests or overly broad questions are considered off-topic.Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.We will keep this issue on file in case our great volunteers come up with ideas for you.Carlos - MSFT | Microsoft Community Support Specialist