Ytukyg

I used to play around with VBA years ago and have not had a need for it until now.

Essentially I am just creating a data entry form for our production guys.

What I am trying to make happen is, each time the user clicks ok, it adds data to a new line under the previous one.

I've got it adding a line, but it just keeps overwriting the line with each click of the ok button.

Here is my code

Option Explicit



Private Sub CancelButton_Click()



Unload Me



End Sub



Private Sub ClearButton_Click()



Call UserForm_Initialize



End Sub



Private Sub OKButton_Click()



Dim emptyRow As Long



'Make Shearline active

Shearline.Activate



'Determine emptyRow

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 3



'Transfer information

Cells(emptyRow, 1).Value = datebox.Value

Cells(emptyRow, 2).Value = operatorbox.Value

Cells(emptyRow, 3).Value = customerbox.Value

Cells(emptyRow, 4).Value = schedulebox.Value

Cells(emptyRow, 5).Value = barmarkbox.Value

Cells(emptyRow, 6).Value = bardialist.Value

Cells(emptyRow, 7).Value = offcutusedbox.Value

Cells(emptyRow, 8).Value = qty6mbox.Value

Cells(emptyRow, 9).Value = qty12mbox.Value

Cells(emptyRow, 11).Value = cutlegnthbox.Value

Cells(emptyRow, 13).Value = tagqtybox.Value

Cells(emptyRow, 15).Value = offcutleftbox.Value

Cells(emptyRow, 17).Value = offcutqtybox.Value

Cells(emptyRow, 19).Value = heatbox.Value



End Sub



Private Sub UserForm_Initialize()



bardialist.AddItem "N10"

bardialist.AddItem "N12"

bardialist.AddItem "N16"

bardialist.AddItem "N20"

bardialist.AddItem "N24"

bardialist.AddItem "N28"

bardialist.AddItem "N32"

bardialist.AddItem "N36+"



'Empty Date

datebox.Value = ""



'Empty Operator

operatorbox.Value = ""



'Empty customer

customerbox.Value = ""



'Empty schedulebox

schedulebox.Value = ""



'Empty Bar Mark

barmarkbox.Value = ""



'Empty Offcut

offcutusedbox.Value = ""



'Empty QTY 6m

qty6mbox.Value = ""



'Empty QTY 12m

qty12mbox.Value = ""



'Empty Cut Legnth

cutlegnthbox.Value = ""



'Empty Tag

tagqtybox.Value = ""



'Empty Offcut left

offcutleftbox.Value = ""



'Empty Offcut QTY

offcutqtybox.Value = ""



'Empty Heat

heatbox.Value = ""



'Set Focus on customer

datebox.SetFocus



End Sub

Close, you need to find the last row..

substitute this

'Determine emptyRow

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 3

For this

'Determine emptyRow

emptyRow  = Sheets("sheet name here").Range("A" & Rows.Count).End(xlUp).Row + 1

Change sheet name here to your actual sheet name. Make sure you keep the "

The reason for the +1 is that the last row code gets the last used cell in that col. as you want the next cell which is blank, we +1

To add, we usually call the formula above LastRow or similar.. If you do any searching this is how it will normally show up

However the better way to do this and safe proof for future is to set your sheets and workbook.

Thus:

DIM wb As Workbook

DIM wks As Worksheet



Set wb As ThisWorkbook

Set wks As wb.Sheets("add sheet name")



'Determine emptyRow

emptyRow  = wks.Range("A" & Rows.Count).End(xlUp).Row + 1