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