Selecting multiple items in excel drop down list

Question

Hello, I created a sheet that I want to select multiple names appear in a single cell. I created the drop downl list and that works. The other day a found a VBA code to put in to the sheet and it worked. This allowed me to select multiple names in a single cell. Today however, this doesnt work??? The code is still there but when I try to select multiple names it just replaces the first name selected.

Here is the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)

' Developed by Contextures Inc.

' www.contextures.com

Dim rngDV As Range

Dim oldVal As String

Dim newVal As String

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then

   'do nothing

Else

  Application.EnableEvents = False

  newVal = Target.Value

  Application.Undo

  oldVal = Target.Value

  Target.Value = newVal

  If Target.Column = 3 Then

    If oldVal = "" Then

      'do nothing

      Else

      If newVal = "" Then

      'do nothing

      Else

      Target.Value = oldVal _

        & ", " & newVal

'      NOTE: you can use a line break,

'      instead of a comma

'      Target.Value = oldVal _

'        & Chr(10) & newVal

      End If

    End If

  End If

End If

exitHandler:

  Application.EnableEvents = True

End Sub

Any Ideas what I am doing wrong?

Answer 1

OK here is my original code that worked at one point:

Private Sub Worksheet_Change(ByVal Target As Range)

' Developed by Contextures Inc.

' www.contextures.com

Dim rngDV As Range

Dim oldVal As String

Dim newVal As String

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then

   'do nothing

Else

  Application.EnableEvents = False

  newVal = Target.Value

  Application.Undo

  oldVal = Target.Value

  Target.Value = newVal

  If Target.Column = 3 Then

    If oldVal = "" Then

      'do nothing

      Else

      If newVal = "" Then

      'do nothing

      Else

      Target.Value = oldVal _

        & ", " & newVal

'      NOTE: you can use a line break,

'      instead of a comma

'      Target.Value = oldVal _

'        & Chr(10) & newVal

      End If

    End If

  End If

End If

exitHandler:

  Application.EnableEvents = True

End Sub

Today I opened a new workbook and put this code in and it does not work. Also on my desktop there is ! on the workbook I saved the other day??

Thank you for your help.

Answer 2

It is possible that your events have been disabled. Try running this macro:

Sub ResetEvents()

Application.EnableEvents = True

End Sub

Answer 3

If your code quits working you should post the code here.

There is probably a reason and rather than reset events manually you should look to prevention or at least have an errortrap that reenables events.

Gord

Answer 4

Copy my code. Use Alt-F11 to go to the VBE, then Press Ctrl-R to open the project explorer. Find your workbook's project (by name) and select it, then use the Insert menu / Module to insert a standard codemodule. When you do that, an empty code pane should appear on the right. Select it, paste the code, put your cursor in the code, and press F5 to run the code.

It appears (to me , at least) that your code was interrupted somehow, leaving events disabled. Closing Excel and re-opening it resets events to their default (enabled) so it is likely that if the code is working now, it will continue to work. If it stops again, try my macro, and see it it helps.

Answer 5

I am a total rookie when it comes to this stuff so I will need some help. I just copied and pasted the first VBA code and it worked...much to my surprise! So how/where do I put this one in??