0

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
Comintern
  • 21,855
  • 5
  • 33
  • 80
John
  • 15
  • 3
  • See [How can I find last row that contains data in the Excel sheet with a macro?](https://stackoverflow.com/q/71180/4088852) – Comintern Nov 26 '18 at 05:21
  • 2
    Try different method to locate the bottom empty line, such as `emptyRow = WorksheetObject.Cells(Rows.Count,"A").End(xlUp).Row` – PatricK Nov 26 '18 at 05:21

1 Answers1

-2

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
alowflyingpig
  • 730
  • 7
  • 18
  • 1
    Your `Rows.Count` is unqualified – JohnyL Nov 26 '18 at 05:54
  • @JohnyL I tested it and it works. Suggest retrying and possibly `debug.print emptyRow` to confirm – alowflyingpig Nov 26 '18 at 06:02
  • 2
    @alowflyingpig what JohnyL is trying to point out is that it might work today, but may fail in the future when the ActiveSheet is changed while the macro is running. This could easily occur when you have more than one workbook open and multiple macros/events are running. Fully qualifying all ranges protects against this. – AJD Nov 26 '18 at 06:12
  • @AJD that is exactly what I'd do also.. I'm not here to re-write the OP's code, but to lend a hand to help make it work. My answer will do that. To satisfy the _correct_ way of doing it I will write a new answer :) – alowflyingpig Nov 26 '18 at 06:16
  • @alowflyingpig your answer worked and has been working since. thanks very much, it had me scratching my head for a while – John Dec 07 '18 at 01:09
  • @John glad I could help you mate. If you could please help me and up vote my answer. Not sure why people want to downvote a correct answer.. Cheers. – alowflyingpig Dec 08 '18 at 07:27
  • @alowflyingpig I've tried to but i'm still too noob here for it to count – John Dec 09 '18 at 20:59