Update range for each selected item in Multi Select Listbox

[sharky12345]

I need to update a column for each item selected in a Multi Select Listbox on a Userform.

I'm using this to load the Listbox;

With Sheet5
Dim Cl As Range
Dim Lst As String
For Each Cl In Range("A2:A42")
 If Cl <> "" And Cl.Offset(, 2).Value > 1 Then
 If Lst = "" Then
Lst = Cl.Value
Else
 Lst = Lst & "," & Cl.Value
 End If
 End If
 Next Cl
 ListBox1.List = Split(Lst, ",")
 End With

I need to update column F for each selected value with "YES" if someone can show me how please?

 

[robofsword2]

I appreciate your patience. It hasn't been long since I learned to code on my own, and I'm afraid that I may be jumping too far and too fast for me to actually understand what the codes do.

So the code I use to populate the userform with dates that correspond to the user is also the userform initialize script:

Private Sub UserForm_Initialize()


Dim rng As Range
Dim Lastrow As Long
Dim i As Long


Lastrow = Range("b" & Rows.Count).End(xlUp).Row
    
For i = 1 To Lastrow
    Set rng = Range("a" & i)
    If rng.Offset(0, 2) = Environ("username") Then
        ListBox1.AddItem rng.Value
    End If
Next i


End Sub

And now my goal is to write a code into a button in that userform that writes cancel in col D for each of the dates selected in the listbox. I know that I'm supposed to be writing a loop, but I'm not sure how exactly I should be looping through entries in a listbox or if I may be overly complicating my situation.

 

[My Aswer Is This]

Try this:
Replace Rob with

Environ("username")

Private Sub CommandButton6_Click()
'Modified  8/13/2018  11:01:21 PM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "b").End(xlUp).Row
Dim i As Long
Dim b As Long
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = True Then
            
                For b = 1 To Lastrow
                    If Cells(b, 3).Value = "Rob" And Cells(b, 1) = DateValue(ListBox1.List(i)) Then Cells(b, 4).Value = "Cancelled By Assoc"
                Next
            End If
        
        Next
        
End Sub

​

 

[robofsword2]

Amazing, it works just the way I needed it to. I knew I had to loop each selected value in the listbox through the dates in col A, so I tried to come up with this code on my own yesterday but I could not find out why it wouldn't work even if my life depended on it.

Dim i As Long
Dim rng As Long
rng = Range("a" & Rows.Count).End(xlUp).Row
For i = 0 To ListBox1.ListCount - 1
    With ListBox1
    If .Selected(i) = True Then
        For Each cell In Range("a1:a" & rng).Cells
            If cell.Value = .list(i) Then
                If cell.Offset(0, 2).Value = Environ("username") Then
                    cell.Offset(0, 3).Value = "Cancelled By Assoc"
                End If
            End If
        Next cell
    End If
    End With
Next i

Thank you for your help and may the deities of Excel keep you throughout your days.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

I see your not using the script I provided. But it's good to see you know how to write scripts the way you want. There are always 10 ways to do everything in Excel. But I guess my script helped you achieve your goal.

 

[robofsword2]

Sorry, it looks like my last response made the wrong impression. I am using the exact script you made and I sincerely appreciate it. I just posted my non-functioning code as comparison to yours so I (and maybe others) could see where I failed.

 

[My Aswer Is This]

That's great. I'm really glad I have things working for you. It took me a little while to figure it out myself. I had not worked with Multiselect listbox's in a while so I had to refresh my memory. So working on this project helped us both. Take care.