I currently have a macro that will enable me to select multiple words from a drop down list and places them in one cell separated by commas (no duplicates allowed). So for example your name in cell A1 and the next cell B1 is a drop down list and you select any state you have visited (B1 shows "Alabama, Alaska, Arizona, Arkansas..."). Now, if I accidentally select a state that I did not want, the only way I remove the unwanted word is to delete the whole cell and re-select all the states (tedious). If I try to delete the one state in the cell I will get an error saying "the value you entered is not valid. A user has restricted values that can be entered into this cell." Is there a way if I can select the state again from the drop down list and it will remove from the cell? Or if there are other options I can see if I can work around it but I would like some feedback.
Macro enabling multiple selections in a drop down list to be placed in one cell
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Bookmarks