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

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
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.

1596712200982.png

VBA Code:
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
 

Attachments

  • 1596711941976.png
    1596711941976.png
    35.5 KB · Views: 0

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
You should use a listbox to display all the data and one set of textboxes to edit the data.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
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.

1596789308873.png

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.

VBA Code:
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
 

Attachments

  • 1596788585493.png
    1596788585493.png
    30.3 KB · Views: 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Aug 6, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
i keep getting the Run time error "70"
Could not set list property when i use the code
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
I'm using rowsource to fill the list box would this be the issue?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top