Update column of selected items in Multi Select Listbox

[sharky12345]

I have a Listbox on a Userform which gets it's values from this;


Dim lastrow As Long
With Sheet1
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With Me.ListBox1
.ColumnCount = 15
.ColumnHeads = True
.ColumnWidths = "50;60;50;65;0;0;0;0;0;0;0;0;0;0;0"
.RowSource = Sheet1.Range("A2:O" & lastrow).Address(, , , True)
End With

I need to update column D of each row if the item is selected in the Listbox and have made a little progress, I've got this working, sort of, but it only updates the row for the first selected item and doesn't loop through them all of someone can help? The code I have so far is here;


Dim rngSearch As Range
Dim rngFound As Range
Dim i As Long
CheckedCalendarFrm.Show
Set rngSearch = Sheet1.Range("B2:B25")
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Set rngFound = rngSearch.Find(what:=.List(i, 1), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
If Not rngFound Is Nothing Then
rngFound(1, 3).Value = TextCheckDate
End If
End If
Next i
End With

Also posted here: https://www.mrexcel.com/forum/excel-questions/990715-update-column-selected-items-multi-select-listbox.html

[mancubus]

check if the second column value .List(i, 2) of selected item(s) exists in B2:B25

you may upload your workbook.

[sharky12345]

Thanks - I don't understand why you're asking me to check that .List(i, 2) exists because that doesn't feature anywhere in the code?

I can't upload a workbook at this stage, I'll have to sanitise it first.

[snb]

Private sub Listbox1_change()
if listbox1.selected(listbox1.listindex) then listbox1.column(3)=date
End sub

[sharky12345]

Thanks, but I get 'Method or data member not found' on this part;


.Columns(3) =

[sharky12345]

*bump*

[sharky12345]

Wasn't necessary to be rude - I came here for help, not to be chastised like a 4 year old!

In any event, check your suggestion before you reply - it doesn't work and produces an error!

[snb]

it doesn't

[sharky12345]

It does;

18480

but disregard.

[snb]

Don't ever use rowsource to populate a combobox/listbox.

[p45cal]

When part of the .rowsource of a listbox changes it appears to deselect the selection!
So grab a list of selected rows first, then process them.
Just tweaking your code a little:
Dim SelectedRows()
ReDim SelectedRows(1 To 1)
idx = 0
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
idx = idx + 1
ReDim Preserve SelectedRows(1 To idx)
SelectedRows(idx) = i
End If
Next i
Set rngSearch = Sheet1.Range("B2:B25")
TextCheckDate = "zzz" 'readjust this
For Each SelectedRow In SelectedRows
Set rngFound = rngSearch.Find(what:=.List(SelectedRow, 1), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
If Not rngFound Is Nothing Then
rngFound(1, 3).Value = TextCheckDate
End If
Next SelectedRow
End With
but this is not robust, because if you have two values in column B the same, the .Find process will only ever find the first.
The following will circumvent that:
Dim SelectedRows()
idx = 0
ReDim SelectedRows(1 To 1)
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
idx = idx + 1
ReDim Preserve SelectedRows(1 To idx)
SelectedRows(idx) = i
End If
Next i
Set yyy = Range(.RowSource)
TextCheckDate = "zzz"
For Each SelectedRow In SelectedRows
yyy.Cells(SelectedRow + 1, 4).Value = TextCheckDate
Next SelectedRow
' 'reinstate selected items:
' For Each SelectedRow In SelectedRows
' ListBox1.Selected(SelectedRow) = True
' Next SelectedRow
End With

[sharky12345]

Thank you - does exactly what I need.

[p45cal]

Note that I added some commented-out code to reinstate the selected items at the end, if you want.

[sharky12345]

Got that - many thanks.