Modify cell values based on listBoxes

[xPukwe]

Hi everyone,

So i want to initialise the values of some cells based on a selection from a listbox containing the projects and a listbox containing the weeks

I want the user to select a project that he is working on and then the week number (S1,S2,S3,S4) in which he wants to enter his productivity (or the time he has put in effort to work on that specific project),

With those two selections from listboxes i want to update the values of the cells presents in the worksheet,

Here is the code i have written so far but i have a "Run time error '424' : object required"

Private Sub UserForm_Initialize()
   
    Dim Col As New Collection
    Dim r As Long
    Dim i As Long
    Dim WeeksArray As Variant
   
    WeeksArray = Array("S1", "S2", "S3", "S4")
   
    r = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
   
    For i = 7 To r
        Col.Add Item:=Sheet1.Range("B" & i).Value
    Next i
   
    For i = 1 To Col.Count
        Me.Deliverables.AddItem Col(i)
    Next i
   
    Me.Weeks.List = WeeksArray
      
End Sub

Private Sub CmdCheck_Click()

    Dim i As Long
    Dim j As Long
    Dim r As Long

    r = Deliverables.ListCount

    For i = 0 To r
        For j = 0 To 3
            If UserForm1.Deliverables.ListIndex = i And UserForm1.Weeks.ListIndex = 0 Then
                Sheet1.Range("G" & i).Value = TxtProd.Value
            ElseIf UserForm1.Deliverables.ListIndex = i And UserForm1.Weeks.ListIndex = 1 Then
                Sheet1.Range("H" & i).Value = TxtProd.Value
            ElseIf UserForm1.Deliverables.ListIndex = i And UserForm1.Weeks.ListIndex = 2 Then
                Sheet1.Range("I" & i).Value = TxtProd.Value
            ElseIf UserForm1.Deliverables.ListIndex = i And UserForm1.Weeks.ListIndex = 3 Then
                Sheet1.Range("J" & i).Value = TxtProd.Value
            End If
        Next j
    Next i

End Sub