Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub Worksheet_Change(ByVal Target As Range)
- 'Code by Sumit Bansal from https://trumpexcel.com
- ' To make mutliple selections in a Drop Down List in Excel
- Dim Oldvalue As String
- Dim Newvalue As String
- Dim num As Integer
- On Error GoTo Exitsub
- If Target.Address = "$E$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
- num = InStr(Oldvalue, Newvalue)
- If num = 0 Then ' If the element selected isnt already on the selected list
- Target.Value = Oldvalue & ", " & Newvalue
- ElseIf num = 1 Then ' If the element is the first on the list
- If Len(Oldvalue) = Len(Newvalue) Then ' If the element is the only element selected
- Target.Value = Replace(Oldvalue, Newvalue, "")
- Else ' If the element is not the only element selected
- Target.Value = Replace(Oldvalue, Newvalue & ", ", "")
- End If
- ElseIf num > 1 Then ' If the element is not the first
- Target.Value = Replace(Oldvalue, ", " & Newvalue, "")
- End If
- End If
- End If
- End If
- Application.EnableEvents = True
- Exitsub:
- Application.EnableEvents = True
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement