| | 40 | == Multi-Select Dropdowns == |
| | 41 | * http://www.contextures.com/excel-data-validation-multiple.html |
| | 42 | {{{ |
| | 43 | Private Sub Worksheet_Change(ByVal Target As Range) |
| | 44 | ' Developed by Contextures Inc. |
| | 45 | ' www.contextures.com |
| | 46 | 'code runs on protected sheet |
| | 47 | Dim oldVal As String |
| | 48 | Dim newVal As String |
| | 49 | Dim strSep As String |
| | 50 | Dim strType As Long |
| | 51 | |
| | 52 | 'add comma and space between items |
| | 53 | strSep = ", " |
| | 54 | |
| | 55 | If Target.Count > 1 Then GoTo exitHandler |
| | 56 | |
| | 57 | 'checks validation type of target cell |
| | 58 | 'type 3 is a drop down list |
| | 59 | On Error Resume Next |
| | 60 | strType = Target.Validation.Type |
| | 61 | |
| | 62 | If Target.Column = 3 And strType = 3 Then |
| | 63 | Application.EnableEvents = False |
| | 64 | newVal = Target.Value |
| | 65 | Application.Undo |
| | 66 | oldVal = Target.Value |
| | 67 | If oldVal = "" Or newVal = "" Then |
| | 68 | Target.Value = newVal |
| | 69 | Else |
| | 70 | Target.Value = oldVal _ |
| | 71 | & strSep & newVal |
| | 72 | End If |
| | 73 | End If |
| | 74 | |
| | 75 | exitHandler: |
| | 76 | Application.EnableEvents = True |
| | 77 | End Sub |
| | 78 | }}} |
| | 79 | |
| | 80 | |
