Excel userform to save to next free row in another Excel document.

[tripodgod]

Hello there. Forgive me as i am new to forums. I have been trying to find a way to fix my Excel 2010 userform for 3 days now and am getting desperate! I am creating an order number system, that is accessed from one document; New Order Number System.xlsm. I have created the userfrom, got all the vlookups working etc, but the only thing i cannot do is make it save to the next free row in: Order Number Record.xlsm (Which is stored on a network drive). I have written code to open the record, save to the first line, then save and close the form:

'Write record to first line of order number record
Dim nwb As Workbook
Set nwb = Workbooks.Open("M:\Jo Bond\EXPENDITURE\Order Number Record.xlsm")
With nwb.Sheets(1)
    .Range("I2").Value = VerbalForm.TextBox7.Text
    .Range("K2").Value = VerbalForm.PropertyBox.Text
    .Range("M2").Value = VerbalForm.TextBox2.Text
    .Range("B2").Value = VerbalForm.ContractorBox.Text
    .Range("J2").Value = VerbalForm.TextBox6.Text
    .Range("L2").Value = VerbalForm.TextBox1.Text
    .Range("S2").Value = VerbalForm.ComboBox1.Text
    .Range("T2").Value = VerbalForm.TextBox4.Text
    .Range("U2").Value = VerbalForm.Label6.Caption

End With

'Close order number record
nwb.Close True

However as the code shows, it will overwrite every time the save button is pressed. I worked out how to find the next free cell in the active worksheet (New Order Number System.xlsm) with this code:

    'Write chorus code to next free cell in current worksheet order numbers
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Sheet6
    lRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
     'Add data to worksheet
    ws.Cells(lRow, "B") = Me.TextBox7.Value

However i can not translate this code to work with the separate Excel file. I am not looking for someone to do the work, just to point me in the right direction please! I am very new to all this programming!!

Thank you in advance!

 

[mumps]

Try replacing

 .Range("I2").Value = VerbalForm.TextBox7.Text

with

.Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).Value = VerbalForm.TextBox7.Text

In the other lines, change the "I" to match the column.

 

[tripodgod]

Try replacing

 .Range("I2").Value = VerbalForm.TextBox7.Text

with

.Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).Value = VerbalForm.TextBox7.Text

In the other lines, change the "I" to match the column.

Hello mumps. Thank you very much for your reply. I will update my code, and try it at work tomorrow (when i have access to the network drive). I will let you know!

Thank you again.

Arran

 

[Dingoz2012]

Private Sub Submit_Click() '  on submit from the userform command button labelled submit
Dim wb As New Workbook
Dim ws As New Worksheet
Set wb = Workbooks.Open("D:\TestResults.xls") '  Location for the results workbook.
Set ws = wb.Worksheets("Test") ' The sheet name for the results
Dim iRow As Long
iRow = WorksheetFunction.Max(ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row, 8)
If Len(ws.Cells(iRow, 1).Value) = 0 Then ws.Cells(iRow, 1).Value = ws.Cells(iRow - 1, 1).Value + 1  ' finds the next empty row and puts a record number in the first column.  Then the data from the user form 

' copy the data to the database
    
    ws.Cells(iRow, 3).Value = Me.textbox1.Value  ' = the first empty row and column 3.  Start this from 0 or where ever you like
    ws.Cells(iRow, 4).Value = Me.textbox2.Value
    ws.Cells(iRow, 5).Value = Me.textbox3.Value

Someone helped me out with this code ages and ages ago. Haven't looked back since.

 

[tripodgod]

Hi Mumps, thank you the code worked perfectly. Thanks for the reply Dingoz2012, if i have any trouble ill give it a go!

Cheers