Multiple selections(add,edit,remove)


New Member
May 26, 2016
Hello everyone,

I am trying to select multiple items from drop down list and to add values to the same cell(separated by comma).
Something like described here(Add Values To the Same Cell section):

Because I don't have experience with excel or visual basic it's hard to understand. On posted page I found sample file where you can add and remove items only from drop down list(keyboard option is not allowed).
From some reason this doesn't work for me(keyboard option is allowed). I spent lot of time on debugging this, but unsuccessfully. Can anyone help me with this?

[/I]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
If Target.Count > 1 Then GoTo exitHandler

lType = Target.Validation.Type
If lType = 3 Then
    Application.EnableEvents = False
    newVal = Target.Value
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 3 Then
        If oldVal = "" Then
            'do nothing
            If newVal = "" Then
                'do nothing
                On Error Resume Next
                Ar = Split(oldVal, ", ")
                strVal = ""
                For i = LBound(Ar) To UBound(Ar)
                    Debug.Print strVal
                    Debug.Print CStr(Ar(i))
                    If newVal = CStr(Ar(i)) Then
                        'do not include this item
                        strVal = strVal
                        lCount = 1
                        strVal = strVal & CStr(Ar(i)) & ", "
                    End If
                Next i
                If lCount > 0 Then
                    Target.Value = Left(strVal, Len(strVal) - 2)
                    Target.Value = strVal & newVal
                End If
            End If
        End If
    End If
End If

  Application.EnableEvents = True
End Sub

If I understand just this line of code is changeable(you pass desired column): If Target.Column = 4 Then ?

Also is there any way to expand this code to work next things:
1) You can add and remove items from drop down list
2) You can add, remove and edit items from keyboard but if I enter some value which is not valid error message is show?

Because I can't figure how to do step 2 correctly - there are some strange behaviour(if I remove some item excel duplicates items in column or if I enter some value I can't change it), I tried to run this code example where keyboard changes are disabled.

I will really appreciate any help, and sorry for bad English.

Forum statistics

Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...