Advertisement
Guest User

Code for select multiple elements in excel drop list

a guest
Jul 25th, 2018
2,044
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3. 'Code by Sumit Bansal from https://trumpexcel.com
  4. ' To make mutliple selections in a Drop Down List in Excel
  5.  
  6. Dim Oldvalue As String
  7. Dim Newvalue As String
  8. Dim num As Integer
  9.  
  10. On Error GoTo Exitsub
  11. If Target.Address = "$E$2" Then
  12.     If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
  13.     GoTo Exitsub
  14.     Else: If Target.Value = "" Then GoTo Exitsub Else
  15.         Application.EnableEvents = False
  16.         Newvalue = Target.Value
  17.         Application.Undo
  18.         Oldvalue = Target.Value
  19.         If Oldvalue = "" Then
  20.             Target.Value = Newvalue
  21.         Else
  22.             num = InStr(Oldvalue, Newvalue)
  23.             If num = 0 Then ' If the element selected isnt already on the selected list
  24.              Target.Value = Oldvalue & ", " & Newvalue
  25.             ElseIf num = 1 Then ' If the element is the first on the list
  26.              If Len(Oldvalue) = Len(Newvalue) Then ' If the element is the only element selected
  27.                Target.Value = Replace(Oldvalue, Newvalue, "")
  28.               Else                                  ' If the element is not the only element selected
  29.                Target.Value = Replace(Oldvalue, Newvalue & ", ", "")
  30.               End If
  31.             ElseIf num > 1 Then  ' If the element is not the first
  32.              Target.Value = Replace(Oldvalue, ", " & Newvalue, "")
  33.             End If
  34.         End If
  35.     End If
  36. End If
  37. Application.EnableEvents = True
  38. Exitsub:
  39. Application.EnableEvents = True
  40. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement