Creating a VBA code for a cell with a dropdown list to allow multiple selections

[griggsa14]

I have a worksheet where I have several vba codes built in already, but I have one cell where I put a dropdown list that I need it to be able to select multiple items from the checklist. Every code I have tried only allows me to localize the solution to once cell, but I need it to go down the whole column. Whenever I have tried to make it a column selection it still only works in the one cell. The column I need it in is I starting in I2 and going down the rest of the column. I already have a data validation in the cells so the checklist works in all of them for a single selection. Any help would be awesome.

[p45cal]

I think you'll need to attach a workbook containing your setup and what you want.

[griggsa14]

I was unable to upload the file for whatever reason it would never show in the attachment tool so I built a quick replica below and then pasted my current VBA codes. In Column A (Far left) I have a dropdown with the options I put in there. I also have a vba code in there to allow a cut and past based on a trigger of the selection status to move it to one of the 5 workbook tabs (later I will need to figure out how to make more than just the completed option work to include Denied and Mistake-Abandoned). The tabs I have from left to right are Overview, Pending, Completed, Denied, Mistake-Abandoned. In column I there is another dropdown with the options I pasted in there as well. This dropdown needs to be able to have multiple selections made instead of just making the selection once. I need to be able to add steps as they progress all localized to one cell. I used data validation for the dropdown and was able to get it working in cell I2 but have been unable to make that coding go all the way down column I. I need it to be in every cell in column I with the same dropdown options. The columns are A-N.






Status
Partner
Property
Applicant
Roommates
App. Started
App. Date
Lease Date
Steps
Notes
Rent Amount
Approval
Approval Time Stamp
Purchase Date




-


Pending


Pending- Awaiting Documents


Pending- Awaiting Lease Details


Pending- Quote Sent


Pending- Invoice Sent


Completed


Denied


Mistake-Abandoned













-


Partner Emailed


Resident Emailed


UW Summary Sent


Agreement Sent


Invoice Sent


Bond Sent












Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, i As Long
Set R = Intersect(Range("A:A"), Target)
If Not R Is Nothing Then
For i = R.Count To 1 Step -1
If R(i).Value = "Completed" Then
Application.EnableEvents = False
With R(i).EntireRow
.Copy Destination:=Sheets("Completed").Cells(Sheets("Completed").Rows.Count, _
"A").End(xlUp).Offset(1, 0)
.Delete
End With
End If
Next i
End If
Application.EnableEvents = True




Dim Oldvalue As String
Dim Newvalue As String


On Error GoTo Exitsub
If Target.Address = "$I$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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub