CODEWITHSUNDEEP
excelvba
Konstantin
Konstantin

Reputation: 13

How do I get two Wocksheet_Changes in one sheet for multiple selection in a dropdown

I would like to have a multiple selection choise from a dropdown for multiple columns on one Excel sheet. I found a code for a multiple selection from a dropdown in one column per sheet, but i need five. The Code it self works for one column.

I already tried to name the Worksheet_Changes in

that didn't work. The result is that I can't choose multiple Names for one cell from a dropdown

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim wert_old As String
    Dim wertnew As String

    On Error GoTo Errorhandling

    If Not Application.Intersect(Target, Range("B4:B999")) Is Nothing Then
        Set rngDV = Target.SpecialCells(xlCellTypeAllValidation)
        If rngDV Is Nothing Then GoTo Errorhandling

        If Not Application.Intersect(Target, rngDV) Is Nothing Then
            Application.EnableEvents = False
            wertnew = Target.Value
            Application.Undo
            wertold = Target.Value
            Target.Value = wertnew

            If wertold <> "" Then
                If wertnew <> "" Then
                    Target.Value = wertold & ", " & wertnew
                End If
            End If
        End If

        Application.EnableEvents = True
    End If

In the end I would like to select multiple names form a dropdown selection, in five different columns

If Not Application.Intersect(Target, Range("B4:B999")) Is Nothing Then

If Not Application.Intersect(Target, Range("C4:C999")) Is Nothing Then

If Not Application.Intersect(Target, Range("D4:B999")) Is Nothing Then
...
...
...

Upvotes: 1

Views: 66

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Use the Application.Union method to combine the ranges you want to run the code in. And then Intersect them with Target like:

If Not Application.Intersect(Target, Union(Me.Range("B4:B999"), Me.Range("C4:C999"), Me.Range("D4:B999"))) Is Nothing Then

enter image description here

Upvotes: 2

Related Questions