Update range for each selected item in Multi Select Listbox

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,311
Office Version
  1. 2016
Platform
  1. Windows
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;

Code:
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?
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
Not sure what your wanting to do.
Here is a script which loops through the selected values in a Multiselect listbox

Code:
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

New Member
Joined
Oct 28, 2014
Messages
25
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?

PTO Request DateDate SubmittedSubmitterStatus
16-Aug8/12/2018 21:05RobPending
17-Aug8/12/2018 21:05RobPending
17-Aug8/12/2018 21:05SamPending
2-Sep8/12/2018 21:05SamPending

<tbody>
</tbody>

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
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?
 
Last edited:

robofsword2

New Member
Joined
Oct 28, 2014
Messages
25

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 28, 2014
Messages
25

ADVERTISEMENT

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
Code:
environ("username")
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 28, 2014
Messages
25
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
Code:
Cells(0,4).value="Cancelled By Assoc"
Is that correct?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,450
Messages
5,523,065
Members
409,495
Latest member
bkahn

This Week's Hot Topics

Top