Consulting

Results 1 to 3 of 3

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

  1. #1

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

    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.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,591
    I think you'll need to attach a workbook containing your setup and what you want.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •