Data Validation Multi-Select: Have code just need tweaked

z3115

Board Regular
Joined
Nov 1, 2013
Messages
71
Hi All, I have code that allows for selecting multiple items from a data validation list and separates them by a comma. The code is below. It works fine, but there are 2 things a little weird that I am hoping you can help fix:

1) If a user enters the cell (double-clicking or F2) and hits "enter", it duplicates what is currently in the cell. For example, if the drop-down is a bunch of fruit and the cell currently says "Apple, Orange", entering the cell and hitting enter caused the cell to now say "Apple, Orange, Apple, Orange". Is there a way to fix this?

2) I only want each item to be listed a max of 1 time. Currently if a cell says "Apple" and a user selects "Apple" from the drop-down it will update to say "Apple, Apple". Is there a way to eliminate this? (Bonus point if clicking an item already in the cell actually REMOVES it from the list - so if the cell says "Apple, Orange, Banana" but I want it to only say "Orange, Banana" all I have to do is click "Apple" from the drop down).

The workbook will be used by a lot of people not familiar with Excel, so I'm trying to error-proof it as much as possible. Thank you for your help!

Code:
[COLOR=#333333][FONT=Menlo]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]' 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 [COLOR=#333333][FONT=Menlo]End Sub[/FONT][/COLOR]
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,122,408
Messages
5,595,961
Members
414,035
Latest member
billbumkins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top