Update Information with Multi Select List Box

[aroig07]

Hi all !!!

I am just starting off with VBA and have made a couple of userforms, but ran into a question when trying to update the information based on multiple selections in a ListBox. I have a userform that shows in a list box all of the jobs that are due this week, if for some reason I do not receive the materials I want to change the status to pending. I had a combo box previously which updated the pertinent information, but going one by one was taking a long time and I decided to change to ListBox thinking the code would be the same but it is not working. The macro runs without debuging but it does not update the column to "Pending".

Here is my code as of now:

Private Sub CommandButton4_Click()


'Click to update the status of the jobs in the master job trail to say they are pending
g = 1
Do
DoEvents
g = g + 1
p = Sheets("Master Job Trail").Range("A" & g)


If p = ListBox1.Text Then
Sheets("Master Job Trail").Range("S" & g) = TextBox6.Text
End If


Loop Until p = ""


MsgBox "Worked was added as pending", vbInformation
ThisWorkbook.Save


Unload Pending


End Sub

 

[aroig07]

By JobName which is a combination of text and numbers in order to update the status to Pending on each of the jobs that are selected in the list box.

 

[Fluff]

Which line is giving the error?

 

[aroig07]

Set Fnd = ws.Range("A:A").Find(.List(i), , xlWhole, , , False, , False)

Here is the entire code I have copied:

Private Sub CommandButton4_Click()


Dim i As Long
Dim Fnd As Range
Dim ws As Worksheet


Set ws = Sheets("Master Job Trail")
With Me.ListBox1
For i = 2 To .ListCount - 1
If .Selected(i) Then
Set Fnd = ws.Range("A:A").Find(.List(i), , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then Fnd.Offset(, 19).Value = Me.TextBox6
End If
Next i
End With

MsgBox "Trabajo fue añadido a los trabajos que están pendientes", vbInformation
ThisWorkbook.Save

End Sub

 

[Fluff]

You're missing a comma, it should be

         Set Fnd = ws.Range("A:A").Find(.List(i), , , xlWhole, , , False, , False)

Also it should be

For i = [COLOR=#b22222]0[/COLOR] To .ListCount - 1

otherwise it will ignore the first 2 items in the listbox

 

[aroig07]

Thank you so much ! Did not catch the extra comma. Works perfectly !!!

 

[Fluff]

You're welcome & thanks for the feedback