EXCEL - Editing macro for selecting multiple items from a drop down list.

The sample worksheet is attached. Any ideas?

You have the newVal and oldVal set to the same Target.Value.  Since it set to run each time there's a change in the worksheet, you cannot delete it since technically a deletion is still a change.

You'll need to add a clause that ensures there aren't two of the same names in any cell.  Or perhaps add a comparison exit that won't allow duplicate entries.

sbjmurrieta,

Please explain what you are wanting the Workseet_Change() macro to do as it is not at clear what it's purpose is.

If you use data validation then that should be enough to control the entry of specific data to those cells. You should not need a separate Workseet_Change() macro to check those cells. Thus please explain.

Patrick

In the Attached Calendar, I want to be able to select Time Off names from the frop down menu.  When I need to select more than one name for any giving day, the macro adds the names together and puts them in the cell. This works well. Unfortunately, when I need to delete a name, I can not. It simply keeps adding  the name again. This makes the calendar useless. Is there any way to fix it?
Calandar--1-.xls

re: "You'll need to add a clause that ensures there aren't two of the same names in any cell.  Or perhaps add a comparison exit that won't allow duplicate entries."

Any ideas on how that would be written? I'm do not know how to write VBA code. Thanks for your help!

Try this.  

Private Sub Worksheet_Change(ByVal Target As Range)
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 > 2 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      If InStr(1, oldVal, newVal, vbTextCompare) <> 0 Then
      Target.Value = oldVal
      GoTo exitHandler
      End If
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

Select allOpen in new window

Dear :John15-16

I tried your cold and there is not a difference that I can see. It still will not allow me to delete a single name from the drop down list.

I added the section below.  It will not allow you to enter a duplicate name so there should be no need to delete one.  If you need to be able to delete I will have to add another section of code, but I will have to research how that is going to work later.

Make sure you enter this code before you click on any cells, otherwise the old code will run instead of this.

      If InStr(1, oldVal, newVal, vbTextCompare) <> 0 Then
      Target.Value = oldVal
      GoTo exitHandler
      End If

Where would this code be entered? Does it replace any of the old code?

The issue is I would still need to be able to delete one if someone time off changed. So making it so you can add a person more than once is also helpful, but I would still need to be able to delete a name if necessary. Thanks for your help!

This is very cool. Still wish I could have used drop down menus, as it is much cleaner. But this works. Thank you for all your help!

sbjmurrieta,

Thanks for the grade.

>Still wish I could have used drop down menus, as it is much cleaner.

Not too sure what you mean but in fact using Data Validation in many cells with a List that needs updating if someone joins or leaves is actually a pain. With using VBA in my solution you only have to make one change on Sheet2. I reckon that is much 'cleaner'.

Patrick