VBA Userform that pulls data from worksheet and inserts in to the same rows

[vhdhfox]

Hi,

Im creating a userform that pulls lines of data from a worksheet based on 1 criteria at the moment (working on second criteria).
im having trouble inserting the data using criteria's to determine which cells the new data populates.

Private Sub CommandButton1_Click()

    TextBox3.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 3)
    TextBox6.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 6)
    TextBox7.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 7)
    TextBox8.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 8)
    TextBox9.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 9)
    TextBox10.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 10)
    TextBox11.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 11)
    TextBox12.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 12)
    TextBox13.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 13)
    TextBox14.Value = Application.WorksheetFunction.Index(Sheets("Allocate").Range("A2:N100"), Application.WorksheetFunction.Match(TextBox4.Value, Sheets("allocate").Range("D:D"), 0), 14)

End Sub

This is the userform and code im using at the moment to pull the data from the worksheet, Column A,B,E are left empty as these are the columns i want to populate using the userform above.

once there is data in "Date" "Num" and "Time" i want to be able to insert these values in to the empty cells on the worksheet using the "Update" CmdButton.

Also with the work sheet there could be 1-8 cells with the exact same data hence why the userform has so many lines, i was planning to use "Line" as a second reference so it would be easier to insert the new data in too A,B,E

 

[Norie]

You should use a listbox to display all the data and one set of textboxes to edit the data.

 

[vhdhfox]

You should use a listbox to display all the data and one set of textboxes to edit the data.

Hi Norie,

Thank you for replying.
Using a listbox is definitely a much better idea for this thanks for that idea.
So i have removed all text boxes except for the first row of text boxes as i want to use these to edit the selected line in the list box.

this is how i have it setup now.

Currently i can view the data in those text box fields using the code below but not quite sure how i can edit them and have the new data populate the line im updating.

Private Sub cmdEdit_Click()
    If AllocateBox1.ListIndex <> -1 Then
        With AllocateBox1
            TextBox1.Value = .List(.ListIndex, 0)
            TextBox2.Value = .List(.ListIndex, 1)
            TextBox3.Value = .List(.ListIndex, 2)
            TextBox4.Value = .List(.ListIndex, 3)
            TextBox5.Value = .List(.ListIndex, 4)
            TextBox6.Value = .List(.ListIndex, 5)
            TextBox7.Value = .List(.ListIndex, 6)
            TextBox8.Value = .List(.ListIndex, 7)
            TextBox9.Value = .List(.ListIndex, 8)
            TextBox10.Value = .List(.ListIndex, 9)
            TextBox11.Value = .List(.ListIndex, 10)
            TextBox12.Value = .List(.ListIndex, 11)
            TextBox13.Value = .List(.ListIndex, 12)
            TextBox14.Value = .List(.ListIndex, 13)
        End With
        
    End If
    
End Sub

 

[Norie]

There are various ways you can go about this.

One would be to add a button that 'sends' the data from the textboxes back to the listbox.

The code for that would basically be the reverse of the code for the cmdEdit button.

Private Sub cmdSave_Click()

    If AllocateBox1.ListIndex <> -1 Then
    
        With AllocateBox1
            .List(.ListIndex, 0) = TextBox1.Value
            .List(.ListIndex, 1) = TextBox2.Value
            .List(.ListIndex, 2) = TextBox3.Value
            .List(.ListIndex, 3) = TextBox4.Value
            .List(.ListIndex, 4) = TextBox5.Value
            .List(.ListIndex, 5) = TextBox6.Value
            .List(.ListIndex, 6) = TextBox7.Value
            .List(.ListIndex, 7) = TextBox8.Value
            .List(.ListIndex, 8) = TextBox9.Value
            .List(.ListIndex, 9) = TextBox10.Value
            .List(.ListIndex, 10) = TextBox11.Value
            .List(.ListIndex, 11) = TextBox12.Value
            .List(.ListIndex, 12) = TextBox13.Value
            .List(.ListIndex, 13) = TextBox14.Value
        End With
        
    End If
    
End Sub

 

[vhdhfox]

i keep getting the Run time error "70"
Could not set list property when i use the code

 

[vhdhfox]

I'm using rowsource to fill the list box would this be the issue?