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

 

[Fluff]

How are you loading the Listbox?

 

[aroig07]

How are you loading the Listbox?

Private Sub UserForm_Initialize()


TextBox4.Value = Format(Now(), "mm/dd/yyyy")
TextBox5.Value = "Not Pending"
TextBox6.Value = "Pending"
 
'define variables so listbox1 populates automatically when the userform initializes with jobs that appear in the weekly schedule
Dim d_Monday As Range
Dim d_Tuesday As Range
Dim d_Wednesday As Range
Dim d_Thursday As Range
Dim d_Friday As Range
Dim d_Saturday As Range
Dim d_Sunday As Range
Dim ws_Weekly As Worksheet
Set ws_Weekly = Worksheets("Weekly Schedule")


'Show the jobs in the Monday column of the weekly schedule
For Each d_Monday In ws_Weekly.Range("Monday")
  With Me.ListBox1
    If d_Monday.Value <> vbNullString Then
    .AddItem d_Monday.Value
    .List(.ListCount - 1, 1) = d_Monday.Offset(0, 1).Value
    End If
  End With
Next d_Monday


'Show the jobs in the Tuesday column of the weekly schedule
For Each d_Tuesday In ws_Weekly.Range("Tuesday")
  With Me.ListBox1
    If d_Tuesday.Value <> vbNullString Then
    .AddItem d_Tuesday.Value
    End If
  End With
Next d_Tuesday


'Show the jobs in the Wednesday column of the weekly schedule
For Each d_Wednesday In ws_Weekly.Range("Wednesday")
  With Me.ListBox1
    If d_Wednesday.Value <> vbNullString Then
    .AddItem d_Wednesday.Value
    End If
  End With
Next d_Wednesday


'Show the jobs in the Thursday column of the weekly schedule
For Each d_Thursday In ws_Weekly.Range("Thursday")
  With Me.ListBox1
    If d_Thursday.Value <> vbNullString Then
    .AddItem d_Thursday.Value
    End If
  End With
Next d_Thursday


'Show the jobs in the Friday column of the weekly schedule
For Each d_Friday In ws_Weekly.Range("Friday")
  With Me.ListBox1
    If d_Friday.Value <> vbNullString Then
    .AddItem d_Friday.Value
    End If
  End With
Next d_Friday


'Show the jobs in the Saturday column of the weekly schedule
For Each d_Saturday In ws_Weekly.Range("Saturday")
  With Me.ListBox1
    If d_Saturday.Value <> vbNullString Then
    .AddItem d_Saturday.Value
    End If
  End With
Next d_Saturday


'Show the jobs in the Sunday column of the weekly schedule
For Each d_Sunday In ws_Weekly.Range("Sunday")
  With Me.ListBox1
    If d_Sunday.Value <> vbNullString Then
    .AddItem d_Sunday.Value
    End If
  End With
Next d_Sunday


End Sub

 

[Fluff]

Ok, try

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 = 0 To .ListCount - 1
      If .Selected(i) Then
         Set Fnd = ws.Range("A:A").Find(.Index(i), , xlWhole, , , , False, , False)
         If Not Fnd Is Nothing Then Fnd.Offset(, 18).Value = Me.TextBox6
      End If
      Next i
   End With
End Sub

 

[aroig07]

Ok, try

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 = 0 To .ListCount - 1
      If .Selected(i) Then
         Set Fnd = ws.Range("A:A").Find(.Index(i), , xlWhole, , , , False, , False)
         If Not Fnd Is Nothing Then Fnd.Offset(, 18).Value = Me.TextBox6
      End If
      Next i
   End With
End Sub

I copied the code above and it comes up with an error on the "Find(.Index(i), , xlWhole, , , , False, , False)" says Method or data memeber not found. Thank you in advance for all of your help on this !!

 

[Fluff]

Oops, it should be

Private Sub CommandButton4_Click()
   Dim i As Long
   Dim Fnd As Range
   Dim ws As Worksheet
   
   Set ws = Sheets("Cover") '("Master Job Trail")
   With Me.ListBox1
      For i = 0 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(, 18).Value = Me.TextBox6
      End If
      Next i
   End With
End Sub

 

[aroig07]

Oops, it should be

Private Sub CommandButton4_Click()
   Dim i As Long
   Dim Fnd As Range
   Dim ws As Worksheet
   
   Set ws = Sheets("Cover") '("Master Job Trail")
   With Me.ListBox1
      For i = 0 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(, 18).Value = Me.TextBox6
      End If
      Next i
   End With
End Sub

Still coming out with error, but now its "subscript out of range" in the same line of code. Do not know why its not working since when I do the watch the Find(.List.......) code gives me the name of a job in the list.

 

[Fluff]

I forgot to change the code back to your sheet, after testing. It should be

Set ws = Sheets("Master Job Trail")

 

[aroig07]

Yeah ! I caught that but still was not working.

 

[Fluff]

What sort of values are you looking for?