PDA

View Full Version : Update Worksheet from Listbox selected items



sharky12345
10-30-2015, 08:34 AM
Can someone help me to get this working and perhaps tidy it up please - I need to update columns F and G of the selected Listbox items with data after an email has been sent.

At the moment it's updating the last one selected but no others;


Row = ListBox1.ListIndex + 2
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Data = ListBox1.List(i, 2)
TextBox1.Value = Data
TextBox2.Value = Row
SendEmail
TextBox1.Value = ""
End If
Next i

Select Case MsgBox("Do you want to mark these Records as Email Sent?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Update email status")
Case vbYes
Row = ListBox1.ListIndex + 2
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Sheets("Database").Range("F" & Row).Value = "Email Sent"
Sheets("Database").Range("G" & Row).Value = Now()
End If
Next i


Case vbNo

End Select

Posted elsewhere: http://www.mrexcel.com/forum/excel-questions/898094-update-worksheet-listbox-selected-items.html

Kenneth Hobs
10-30-2015, 05:38 PM
Would changing & row to i + 1 help?

Or, would you want to insert the selected values to the next blank row in those two columns?

sharky12345
10-31-2015, 01:33 AM
Hi Kenneth - no, I need to update the row of the selected items so that when the Userform is displayed the next time those values don't appear, (I have a filter in place so that the Listbox only populates values where column F is blank).

I don't know enough about changing it to your first line so will give that a go - it may well be that simple!

sharky12345
10-31-2015, 03:34 AM
' i + 1' does it so thank you!