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?

 

[My Aswer Is This]

Not sure what your wanting to do.
Here is a script which loops through the selected values in a Multiselect listbox

Private Sub CommandButton2_Click()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Dim i As Long
Dim x As Long
x = 1
   For i = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(i) Then
   Cells(Lastrow, 2).Value = ListBox1.List(i)
   Lastrow = Lastrow + 1
   ListBox1.Selected(i) = False
   End If
    Next
End Sub

 

[robofsword2]

I know this thread is four years old, but it's the closest to my query.

I'm trying update in column D (Status) based on the user selection in the listbox. For example, if Rob cancels his request for the 17th, column D for that row should write "Cancelled by Assoc". I've tried modifying your code, but it doesn't seem to work for me. Help please?

[TABLE="class: grid, width: 402"]
<tbody>[TR]
[TD]PTO Request Date[/TD]
[TD]Date Submitted[/TD]
[TD]Submitter[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]16-Aug[/TD]
[TD="align: right"]8/12/2018 21:05[/TD]
[TD]Rob[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD="align: right"]17-Aug[/TD]
[TD="align: right"]8/12/2018 21:05[/TD]
[TD]Rob[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD="align: right"]17-Aug[/TD]
[TD="align: right"]8/12/2018 21:05[/TD]
[TD]Sam[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD="align: right"]2-Sep[/TD]
[TD="align: right"]8/12/2018 21:05[/TD]
[TD]Sam[/TD]
[TD]Pending[/TD]
[/TR]
</tbody>[/TABLE]

Private Sub CancelButton_Click()


Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "b").End(xlUp).Row
Dim i As Long
Dim x As Long
x = 1
For i = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(i) Then
    If Cells(Lastrow, 1).Value = Environ("username") Then
        Cells(Lastrow, 2).Value = "Cancelled By Assoc"
        Lastrow = Lastrow + 1
        ListBox1.Selected(i) = False
        End If
   End If
Next


End Sub

 

[My Aswer Is This]

Your values are on a sheet and not in a listbox is that correct?
So if Rob cancels his request then column D should say:

Cancelled by Assoc​

If that is what you want then what action indicates Rob wants to cancel?

 

[robofsword2]

Sorry for leaving out some details. There is a listbox in a userform where if user Rob selects a date, then only the entry in column D that corresponds to his name AND the date he selected in the userform listbox should update to "Cancelled by Assoc". The same should also work should the user choose to cancel more than one entry.

 

[My Aswer Is This]

So on your sheet you have what is displayed above

And we are looking at column A B C D

So what are we seeing in the Listbox?

Are we dealing with a Multicolumn Listbox?

And you said:
if user Rob selects a date​

How do we know who the User is?

 

[robofsword2]

The listbox in the userform is populated by entries in Col A that corresponds to the user, and it's a single column listbox. The user is determined through

environ("username")

 

[My Aswer Is This]

Lets look at your script a little:
You have this line of code:

Cells(Lastrow, 2).Value = "Cancelled By Assoc"
​

The 2 means column 2 but I think you want it to be 4 correct?

Learning what these little things mean can help you.
Or maybe you already know this and I'm confused

And you use Lastrow

Lastrow means the lastrow in the column with data in your case your looking for lastrow with data in column B

But in your loop you keep using Lastrow and keep adding to last row

 

[robofsword2]

Thanks for pointing that out! I'm fairly new to VBA, so the codes I use are mostly just copied from solutions I find on online.

So if I wanted to update the adjacent cell in col D for EACH corresponding entry, I would write the code as

Cells(0,4).value="Cancelled By Assoc"

Is that correct?

 

[My Aswer Is This]

I cannot say that for sure. That is just one part of your script that looked wrong to me.

With things like this just test it and see what happens.
Always test things on a copy of your workbook so as not to mess up something important.

It's always hard helping someone when they have a script they found someplace which does not do what they want.

Please show me the code you use to load all the values into the listbox.

And show me any other code you are using with this script.

Is this all your attempting to do or is this just one part to a much larger project your working on?

I know some people pick several pieces of script from numerous sources and try to paste it all together to get to their end result.

I have used multiselect list box's before but not a whole lot. I like learning more myself trying to help people here. So I will attempt to help you here as much as i can.

And the lastrow thing makes me question how your doing things

Normally we would only use that once to find the lastrow in a column with data. But your script keeps adding one to the lastrow.

Please look at each question and try and answer each one.
And who knows. Someone else here on the forum might see our chatter and jump right in with the perfect answer before me.

It's always best to try and understand what scripts are doing so some day you will know how to write your own scripts with little or no help.

When I first began programming and was proud of myself when I could get the computer to Put Hello George on the screen.

I will keep trying to help you and hope you do not just disappear and never check back in here. A lot of posters here do that. I will let you know if I give up trying to help here.