Add data set to next empty row of an excel database

When we create data entry applications in Excel, we need to save data to Excel sheets from VBA forms. Then we can access those stored data later through forms. And we can also use those stored data to generate reports or dashboards as required. So how do we transfer data from VBA forms to Excel sheets? We can’t use the macro recorder to find out this. This needs a custom VBA code. So in this lesson you can learn how to save data from a VBA form to the next empty row of a worksheet.

Let’s consider this sample VBA form.

This form has 7 text fields. Names of the textboxes are as follows.

Field Name	Textbox Name
Order Number	txtOrderNumber
Size	txtSize
Serial Number	txtSerialNumber
Project Number	txtProjectNumber
Type	txtType
Date	txtDate
Surveyor	txtSurveyor

Name of the “Enter” button is cmdEnter. When the user fills the data and clicks the “Enter” button, data should be transferred to the next empty row of the sheet below.

Now let’s look at how to create the code for the “Enter” button. First we need to declare a few variables.

Dim WS_Data As Worksheet
Dim LastRow As Long
Dim NextRow As Long

Name of the worksheet is “Data”. So we can assign the worksheet to the WS_Data variable as follows.

Set WS_Data = Worksheets("Data")

Next we need to find the row number of the last cell having data. We can get it as follows.

'Find last row
LastRow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Now the variable “LastRow” contains the row number of the last cell with data. Then we can get the next row easily like this.

NextRow = LastRow + 1

After that we can write the data from VBA form to the worksheet as follows.

'Enter data to "Data" sheet
WS_Data.Range("A" & NextRow) = txtOrderNumber.Value
WS_Data.Range("B" & NextRow) = txtSize.Value
WS_Data.Range("C" & NextRow) = txtSerialNumber.Value
WS_Data.Range("D" & NextRow) = txtProjectNumber.Value
WS_Data.Range("E" & NextRow) = txtType.Value
WS_Data.Range("F" & NextRow) = txtDate.Value
WS_Data.Range("G" & NextRow) = txtSurveyor.Value

Then we should clear the form after entering the data. So users can enter the next data set to the excel database through VBA form. You can use the below code to clear the textboxes.

'Clear the textboxes
txtOrderNumber.Value = ""
txtSize.Value = ""
txtSerialNumber.Value = ""
txtProjectNumber = ""
txtType.Value = ""
txtDate.Value = ""
txtSurveyor.Value = ""

Finally we can save the workbook and tell the user that data is saved.

ActiveWorkbook.Save
MsgBox "Data saved successfully!", vbInformation, ""

Here is the full code for the “Enter” button.

Private Sub cmdEnter_Click()

Dim WS_Data As Worksheet
Dim LastRow As Long
Dim NextRow As Long

Set WS_Data = Worksheets("Data")
'find last row
LastRow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
NextRow = LastRow + 1

'Enter data to "Data" sheet
WS_Data.Range("A" & NextRow) = txtOrderNumber.Value
WS_Data.Range("B" & NextRow) = txtSize.Value
WS_Data.Range("C" & NextRow) = txtSerialNumber.Value
WS_Data.Range("D" & NextRow) = txtProjectNumber.Value
WS_Data.Range("E" & NextRow) = txtType.Value
WS_Data.Range("F" & NextRow) = txtDate.Value
WS_Data.Range("G" & NextRow) = txtSurveyor.Value

'Clear the textboxes
txtOrderNumber.Value = ""
txtSize.Value = ""
txtSerialNumber.Value = ""
txtProjectNumber = ""
txtType.Value = ""
txtDate.Value = ""
txtSurveyor.Value = ""

'Save workbook
ActiveWorkbook.Save
MsgBox "Data saved successfully!", vbInformation, ""

End Sub