Hi Everyone!
I am looking for help with the following problem. Please provide any code or suggestions:
- I have created a multi-selection data validation drop down list for 2 columns in a worksheet of about 15 columns.
- The text populated by the multi-selection drop down lists is separated by line breaks.
- I would like each new selection from the drop down list to populate a single cell alphabetically. For example, if I select 'Operations' first from the drop down then select 'Certifications', I would like 'Certifications' to populate the cell before 'Operations'. Similarly, if I then select 'Audit' I would like 'Audit' to populate the cell before both 'Certifications' and 'Operations'.
Here is my existing code that allows me to multi-select from a drop down, remove a selection, and separate the selections by a line break:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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
Select Case Target.Column
Case 6, 8
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & vbNewLine, "")
End If
Else
Target.Value = oldVal _
& vbNewLine & newVal
End If
End If
End If
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
Bookmarks