Rachel B
New Member
Hi all,
I'm new to VBA. By looking at a lot of online examples, I've managed to create a UserForm to input data into an Excel table. When I click the "Add" button on the userform, I want it to add a new row to the bottom of the table (above the Totals line) and input the data entered in the userform into that new, blank row. The code that I've written inserts the new row correctly but, instead of putting the data from the userform in that row, it overwrites the last row of the table that already contains data. If I click the "Add" button a second time, it enters a second blank row but overwrites the same line of data.
My code looks like this...
This code is what I've cobbled together from various online examples. It may not be the most efficient way of getting the job done but, aside from the issue described above, it works. I think it's the emptyRow portion that is the problem but I'm not sure how to fix it. Any help you can offer is much appreciated!
Also, while trying to figure this out for myself, I've been searching for a good source that explains the VBA syntax. If I understood what each of the commands in each line of code was doing, I could break it down step by step and see where the problem is. Does anyone have a recommendation?
Thanks!
I'm new to VBA. By looking at a lot of online examples, I've managed to create a UserForm to input data into an Excel table. When I click the "Add" button on the userform, I want it to add a new row to the bottom of the table (above the Totals line) and input the data entered in the userform into that new, blank row. The code that I've written inserts the new row correctly but, instead of putting the data from the userform in that row, it overwrites the last row of the table that already contains data. If I click the "Add" button a second time, it enters a second blank row but overwrites the same line of data.
My code looks like this...
Code:
Private Sub cmdAdd_Click()
Dim emptyRow As Long
'Make Production Schedule sheet active
Sheet1.Activate
'Add new row
Call AddRow
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = txtCustomer.Value
Cells(emptyRow, 2).Value = txtOrder.Value
Cells(emptyRow, 3).Value = txtDateRecd.Value
Cells(emptyRow, 4).Value = txtDateReqd.Value
Cells(emptyRow, 5).Value = ""
Cells(emptyRow, 6).Value = ""
Cells(emptyRow, 9).Value = txtBillet.Value
Cells(emptyRow, 10).Value = ""
Cells(emptyRow, 12).Value = txtSlab.Value
Cells(emptyRow, 13).Value = ""
Cells(emptyRow, 15).Value = txtChip.Value
Cells(emptyRow, 16).Value = ""
Cells(emptyRow, 18).Value = txtFlame.Value
Cells(emptyRow, 19).Value = ""
Cells(emptyRow, 21).Value = txtSandblast.Value
Cells(emptyRow, 22).Value = ""
Cells(emptyRow, 24).Value = txtCut.Value
Cells(emptyRow, 25).Value = ""
Cells(emptyRow, 27).Value = txtPolish.Value
Cells(emptyRow, 28).Value = ""
Cells(emptyRow, 30).Value = txtEdge.Value
Cells(emptyRow, 31).Value = ""
Cells(emptyRow, 39).Value = txtBullnose.Value
Cells(emptyRow, 40).Value = ""
Cells(emptyRow, 42).Value = txtCore.Value
Cells(emptyRow, 43).Value = ""
Cells(emptyRow, 45).Value = txtSeal.Value
Cells(emptyRow, 46).Value = ""
Cells(emptyRow, 33).Value = txtPackage.Value
Cells(emptyRow, 34).Value = ""
Cells(emptyRow, 36).Value = ""
Cells(emptyRow, 48).Value = txtWjb.Value
Cells(emptyRow, 49).Value = "Open"
End Sub
Sub AddRow()
'Add new row to bottom of table
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.EntireRow.Insert
End Sub
This code is what I've cobbled together from various online examples. It may not be the most efficient way of getting the job done but, aside from the issue described above, it works. I think it's the emptyRow portion that is the problem but I'm not sure how to fix it. Any help you can offer is much appreciated!
Also, while trying to figure this out for myself, I've been searching for a good source that explains the VBA syntax. If I understood what each of the commands in each line of code was doing, I could break it down step by step and see where the problem is. Does anyone have a recommendation?
Thanks!