Automatic data entry to a seperate workbook from a userform

[John_McClane]

Hi Guys,

Hope you can help with this one, have been struggling for weeks.

I have a userform, UserForm1, which lives in a spreadsheet called 'Data Entry.xls' There is nothing else on the spreadsheet itself, it's just for the use of a userform.
I would like the user to populate textboxs in UserForm1 but have that update cells in a separate spreadsheet 'Training.xls' in the same directory.

I have this code at the moment to find the next empty row and to input data into it, which is working perfectly to enter data to sheet1 in Data Entry.xls:

Private Sub CommandButton1_Click()

eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1) = TextBox1.Text
Cells(eRow, 2) = TextBox2.Text
Cells(eRow, 3) = TextBox6.Text


End Sub[CODE]

How can i modify it so it would do the same thing, i.e find the next empty row and then populate with what the user types in the textbox but in Sheet1 of 'Training.xls' 

Would 'Training.xls' need to be open? 

Thank you very much in advance, hope that makes sense!

 

[bertie]

Try these modifications. It simply sets up variables for the Closed workbook and worksheet and uses these todetermine where the output goes.

Press F8 to step through the code, edit where necessary.

Private Sub CommandButton1_Click()
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim fileName As String
   
   fileName = ThisWorkbook.Path & "\" & "Training.xls"
   
   Set wb = Workbooks.Open(fileName)
   Set ws = wb.Sheets("Sheet1")
   
   eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   ws.Cells(eRow, 1) = TextBox1.Text
   ws.Cells(eRow, 2) = TextBox2.Text
   ws.Cells(eRow, 3) = TextBox6.Text


   '[optional]
   'wb.Close SaveChanges:=True


   'tidy up
   Set ws = Nothing
   Set wb = Nothing
End Sub

 

[John_McClane]

Thank you very much - am looking at it now.