Excel isn’t a database software but as long as you don’t need to manage millions of data points or have complex, interlocking systems, it can potentially do the job for you — and save you plenty of money. You can create forms, that with the use of visual basic, can populate a database which you can track within Excel and then create reports on later.
Designing a user form and database sheet
Let’s suppose we want to track sales and commission data. A form that someone may fill out could include the following fields:
- Sales Rep
- Store
- Date
- Product
- Sale Amount
Upon entering this data, a user could click on a button to post the data to another sheet within the workbook, thereby adding it to a database. And with more entries, the data can grow over size. We could then use the data to run reports on and to track store sales, calculate commission, and other metrics.
Here’s how I’ve designed this form in Excel:
The ‘Post Data’ button was created using a shape. And it will be attached to a macro, which will then post to the database worksheet.
Next, I’ll create the corresponding fields where these values will post to on a separate sheet, which I’ll call the database sheet. The layout is a simple one which just involves the same headers from the sales entry form.
Populating the database sheet using a macro
Next, I’ll create a macro to copy the data from the sales entry form to the next row in the database sheet.
The following macro will find the next row in the database sheet, and then copy the values from the sales entry form into the corresponding field. I’ve also added a message box at the end to let the user know that the data was posted successfully to the database sheet. My inputs in cells C4:C8.
Sub copyinputs()
Dim cl As Range
Dim nextrow As Double
Dim wsinput As Worksheet
Dim wsdatabase As Worksheet
'set the worksheet variables
Set wsinput = Worksheets("Input")
Set wsdatabase = Worksheets("Database")
'detect the next available row based on values in column A
nextrow = WorksheetFunction.CountA(wsdatabase.Range("A:A")) + 1
'copy values over
wsinput.Range("C4").Copy wsdatabase.Range("A" & nextrow) ' copy the sales rep
wsinput.Range("C5").Copy wsdatabase.Range("B" & nextrow) ' copy the store
wsinput.Range("C6").Copy wsdatabase.Range("C" & nextrow) ' copy the date value
wsinput.Range("C7").Copy wsdatabase.Range("D" & nextrow) ' copy the product
wsinput.Range("C8").Copy wsdatabase.Range("E" & nextrow) ' copy the sale amount
MsgBox "Posted!"
End SubThis macro, called copyinputs still needs to be assigned to the button. To do this, I need to right-click on the button on the sales entry form and select Assign Macro and then select this macro. After doing this, I can start entering data and populating my database. When I finish filling in my data and I click on the Post Data button, I get a message saying it has been posted.
And in my database sheet, it has begun populating the data:
As I enter data and click on the Post Data button, it continues adding to my database:
Since it is always calculating the next row, it isn’t overwriting the existing information.
Additional macros to setup
This current macro is a simple one which just posts the data. It doesn’t do anything besides that. It doesn’t check for if the fields are entered nor does it clear them after they’ve been entered. You can adjust this, however, to make the macro and form more robust.
Here’s a macro which includes the existing code plus code to clear the values after they’ve been entered, assuming the values are in the range C4:C8.
Sub copyinputs()
Dim cl As Range
Dim nextrow As Double
Dim wsinput As Worksheet
Dim wsdatabase As Worksheet
'set the worksheet variables
Set wsinput = Worksheets("Input")
Set wsdatabase = Worksheets("Database")
'detect the next available row based on values in column A
nextrow = WorksheetFunction.CountA(wsdatabase.Range("A:A")) + 1
'copy values over
wsinput.Range("C4").Copy wsdatabase.Range("A" & nextrow) ' copy the sales rep
wsinput.Range("C5").Copy wsdatabase.Range("B" & nextrow) ' copy the store
wsinput.Range("C6").Copy wsdatabase.Range("C" & nextrow) ' copy the date value
wsinput.Range("C7").Copy wsdatabase.Range("D" & nextrow) ' copy the product
wsinput.Range("C8").Copy wsdatabase.Range("E" & nextrow) ' copy the sale amount
'clear the data
wsinput.Range("C4:C8").ClearContents
MsgBox "Posted!"
End SubAdditionally, we can setup rules to make sure that the fields have to be entered fully before clicking on the Post Data button. This can prevent incomplete entries from being posted to the database sheet. Here’s how we can add a code to check if the sales rep field contains a value. This can be repeated for all other required fields:
Sub copyinputs()
Dim cl As Range
Dim nextrow As Double
Dim wsinput As Worksheet
Dim wsdatabase As Worksheet
'set the worksheet variables
Set wsinput = Worksheets("Input")
Set wsdatabase = Worksheets("Database")
'detect the next available row based on values in column A
nextrow = WorksheetFunction.CountA(wsdatabase.Range("A:A")) + 1
'check if the sales rep field contains a value
If wsinput.Range("C4") = "" Then
MsgBox "Please enter a sales rep"
Exit Sub
End If
'copy values over
wsinput.Range("C4").Copy wsdatabase.Range("A" & nextrow) ' copy the sales rep
wsinput.Range("C5").Copy wsdatabase.Range("B" & nextrow) ' copy the store
wsinput.Range("C6").Copy wsdatabase.Range("C" & nextrow) ' copy the date value
wsinput.Range("C7").Copy wsdatabase.Range("D" & nextrow) ' copy the product
wsinput.Range("C8").Copy wsdatabase.Range("E" & nextrow) ' copy the sale amount
'clear the data
wsinput.Range("C4:C8").ClearContents
MsgBox "Posted!"
End Sub
If you like this post on Create a Database Entry Form in Excel to Populate a Sheet Using VBA Macros please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.